Tag: collation

Feed: Planet PostgreSQL. tl;dr; SELECT COUNT(*) FROM (SELECT DISTINCT my_not_unique_indexed_column FROM my_table) t;I have a table that looks like this: songsearch=# d main_songtexthash Table "public.main_songtexthash" Column | Type | Collation | Nullable | -----------+--------------------------+-----------+----------+ id | integer | | not null | text_hash | character varying(32) | | not null | created | timestamp with time zone | | not null | modified | timestamp with time zone | | not null | song_id | integer | | not null | Indexes: "main_songtexthash_pkey" PRIMARY KEY, btree (id) "main_songtexthash_song_id_key" UNIQUE CONSTRAINT, btree (song_id) "main_songtexthash_text_hash_c2771f1f" btree (text_hash) "main_songtexthash_text_hash_c2771f1f_like" btree (text_hash varchar_pattern_ops) Foreign-key ... Read More

Feed: R-bloggers. Author: free range statistics - R. Swings are far from uniformLast week I introduced a Bayesian state space model of two-party-preferred voting intention for Australian federal elections. It treats the surveys from various polling firms as imperfect (potentially systematically imperfect) measurements of an unobserved latent variable of “true” voting intention, which manifests itself only every few years in the form of election results. The model estimates that latent value for every day that measurements exist, and going forward with increasing uncertainty past when there are measurements. The most likely day for the next federal election seems to be ... Read More

Feed: Planet MySQL; Author: MySQL Performance Blog; PREVIOUS POST Following my post MySQL 8 is not always faster than MySQL 5.7, this time I decided to test very simple read-only CPU intensive workloads, when all data fits memory. In this workload there is NO IO operations, only memory and CPU operations. My Testing Setup Environment specification Release | Ubuntu 18.04 LTS (bionic) Kernel | 4.15.0-20-generic Processors | physical = 2, cores = 28, virtual = 56, hyperthreading = yes Models | 56xIntel(R) Xeon(R) Gold 5120 CPU @ 2.20GHz ... Read More

Feed: Databasejournal.com - Feature Database Articles. Author: . Azure provides a number of different methods that can be used in order to implement and configure its resources. Each of the methods differs to some extent in regard to functionality and intended use cases. The Azure portal offers friendly, intuitive interface but lacks automation capabilities. Scripting with Azure PowerShell or Azure CLI facilitates automation but does not scale well due to its limited support for parallelism. The use of REST API and Azure SDKs, while extremely powerful, requires at least some level of programming skills. Azure Resource Manager templates, which are ... Read More

Feed: Planet MySQL; Author: VividCortex; A Guest Post from Jordan Raine, ClioJordan Raine is a staff software developer at Clio, the leading provider of legal practice management, client relationship management, and client intake software. Clio is dedicated to helping lawyers meet client needs and run efficient, profitable practices. Jordan wrote this post to help mySQL developers save time/effort and ultimately improve their customers' experience.  ... Read More

Feed: Planet PostgreSQL. The right application of indexes can make queries blazing fast. Indexes use pointers to access data pages in a speedy fashion. Major changes happened on Indexes in PostgreSQL 11, lots of much awaited patches have been released. Let's have a look at some of the great features of this release. Parallel B-TREE Index Builds PostgreSQL 11 introduced an infrastructure patch to enable parallel index creation. It can be only used with B-Tree index as for now. Building a parallel B-Tree index is two to three times faster than doing the same thing without parallel working (or serial ... Read More

Feed: Planet MySQL; Author: Øystein Grøvlen; FOSDEM is coming up. I do not have a presentation in the MySQL and Friends devroom this year, but it reminded me that I had planned to post a follow-up to my presentation from last year. As part of the presentation, I showed how you can inspect the content of a histogram using the information schema table column_statistics. For example, the following query will show the content of the histogram for the column l_linenumber in the table lineitem of the dbt3_sf1 database: SELECT JSON_PRETTY(histogram) FROM information_schema.column_statistics WHERE schema_name = 'dbt3_sf1' AND table_name ='lineitem' AND column_name = ... Read More

Feed: Planet PostgreSQL. One of the toughest challenges facing database engineers today is ensuring their data is always accessible so they can meet the high-availability  requirements for their applications.While this problem may seem trivial in the world of applications where one can have many instances behind geographically distributed load balancers, in the database world where there is only one version of the data globally, the problem faces many obstacles. PostgreSQL replication has advanced considerably in recent major releases, including continuous improvements to streaming replication and the addition of logical replication in PostgreSQL 10. While these capabilities represent important enhancements to ... Read More

Feed: Planet MySQL; Author: Geir Hoydalsvik; There are over 250 new features in MySQL 8.0. The MySQL Manual is very good, but verbose. This is a list of new features in short bullet form. We have tried very hard to make sure each feature is only mentioned once. Note the similar list for MySQL 5.7. Please download MySQL 8.0 from dev.mysql.com or from the MySQL  Yum,  APT, or SUSE repositories. SQL DML Non-recursive CTEs [] Recursive CTEs [] Window functions [] ORDER BY and DISTINCT with ROLLUP [] LATERAL derived tables [] Outer table references in derived tables [] SQL ... Read More

Feed: Planet PostgreSQL. Yesterday I copied data from the old production server to the new production server. One thing I missed, but did think about at the time, was updating the sequence used by the table in question. Looking at the table definition: freshports.org=# d report_log Table "public.report_log" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+-------------------------------------------- id | integer | | not null | nextval('report_log_id_seq'::regclass) report_id | integer | | not null | frequency_id | integer | | | report_date | timestamp with time zone | | not null | ('now'::text)::timestamp(6) with time zone email_count | integer | ... Read More