Nsryjdtyk

I have a table which includes a multi-column index defined as

CREATE INDEX tab_a_idx1 ON tab_a USING btree (device, fixtime)

The index was chosen deliberately because the majority of the queries run against this table include selection criteria like this

WHERE device = 'xyz' AND fixtime > 'sometime' AND fixtime <= 'someothertime' ORDER BY fixtime;

The table has been clustered on this index in a effort to improve performance.

CLUSTER tab_a USING tab_a_idx1;

Based on the comments and answers in a previous question I've used this query to list my clustered tables, the indexes they're clustered on, and the definitions of those indexes.

SELECT c.oid, c.relname as tablename, x.relname as indexname, z.indexdef

FROM   pg_class c

JOIN   pg_index i ON i.indrelid = c.oid

JOIN   pg_class x ON i.indexrelid = x.oid

JOIN   pg_indexes z ON x.relname = z.indexname

WHERE  c.relkind = 'r' AND c.relhasindex AND i.indisclustered 

And I've been using the pg_stats table to check the correlation of the indexed columns.

The quoted answer states that a correlation close to '1' is good, and as the value get lower the more clustering is indicated.

Immediately after the table was clustered the correlation of the 1st field in the index (device) was low (0.008) and the 2nd one (fixtime) relatively high (0.994).

• If these values are supposed to be close to '1' but aren't, does that mean that a table can't (or shouldn't) be clustered on a multi-column index?


• There are several versions of the tab_a (it's partitioned on fixtime) and I've noticed that the correlation values don't actually seem to vary much between the clustered and un-clustered versions of the table. Does this mean there's no point in clustering on this index?

Thanks

UPDATE - the parent table was created as follows....

CREATE TABLE tab_a 

  ( device  CHAR(6),

    fixTime TIMESTAMP,

    ....lots more fields.....

  )

PARTITION BY RANGE (fixTime);

The individual partitions were created like this

CREATE TABLE tab_a_201704 PARTITION OF tab_a FOR VALUES FROM ('2017-04-01' ) TO (  '2017-05-01' )

And the index used for the clustering like this....

CREATE INDEX tab_a_201704_idx2 ON tab_a_201704 (device, fixTime);

And the command to do the cluster....

CLUSTER tab_a_201704 USING tab_a_201704_idx2 ;