Primary key index not getting used

Question

I created a table with composite primary key(of 3 columns) in postgresql. Default index not getting used, if use subsets in query which doesn't contains leading column. Which is not the case if we create index explicitly(index will be used for any subsets).

By default postgres will create an index on the primary key. But as postgres document says

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

If query doesn't include leading columns then also indexing will get used(if we create index explicitly), But indexing is not getting used when we are trying with subsets of default primary key index.

Following are the schema and queries which not working with subsets.

# \d client_data
              Table "public.client_data"
       Column       |         Type          | Modifiers 
--------------------+-----------------------+-----------
 macaddr            | character varying(64) | not null
 ts                 | bigint                | not null
 interval           | smallint              | not null
 snr                | smallint              | not null
 rx_rate            | bigint                | 
 tx_rate            | bigint                | 
 rx_data            | bigint                | 
 tx_data            | bigint                | 

Indexes:
    "client_data_pkey" PRIMARY KEY, btree (macaddr, ts, interval)

If we specify all the primary key columns then query planner will use indexing

# explain analyze select count(*) from client_data where macaddr='a:b:c' and ts=346783556 and interval=5;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.60..8.61 rows=1 width=0) (actual time=0.040..0.041 rows=1 loops=1)
   ->  Index Scan using client_data_pkey on client_data  (cost=0.00..8.59 rows=1 width=0) (actual time=0.037..0.037 rows=0 loops=1)
         Index Cond: (((macaddr)::text = 'a:b:c'::text) AND (ts = 346783556) AND ("interval" = 5))
 Total runtime: 0.096 ms
(4 rows)

But if we specify subsets, query planner won't use indexing

# explain analyze select count(*) from client_data where ts=346783556;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16176.01..16176.02 rows=1 width=0) (actual time=78.937..78.938 rows=1 loops=1)
   ->  Seq Scan on client_data  (cost=0.00..16175.92 rows=36 width=0) (actual time=78.932..78.932 rows=0 loops=1)
         Filter: (ts = 346783556)
 Total runtime: 78.975 ms
(4 rows)


# explain analyze select count(*) from client_data where ts=346783556 and interval=5;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=17639.11..17639.12 rows=1 width=0) (actual time=78.815..78.815 rows=1 loops=1)
   ->  Seq Scan on client_data  (cost=0.00..17639.11 rows=1 width=0) (actual time=78.810..78.810 rows=0 loops=1)
         Filter: ((ts = 346783556) AND ("interval" = 5))
 Total runtime: 78.853 ms
(4 rows)

But if we use leading column(macaddr) with either ts or interval, indexing will get used.

# explain analyze select count(*) from client_data where macaddr='a' and ts=346783556;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8.59..8.60 rows=1 width=0) (actual time=0.055..0.056 rows=1 loops=1)
   ->  Index Scan using client_data_pkey on client_data  (cost=0.00..8.59 rows=1 width=0) (actual time=0.051..0.051 rows=0 loops=1)
         Index Cond: (((macaddr)::text = 'a'::text) AND (ts = 346783556))
 Total runtime: 0.103 ms
(4 rows)


# explain analyze select count(*) from client_data where macaddr='a' and interval=56;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=56.15..56.16 rows=1 width=0) (actual time=0.021..0.022 rows=1 loops=1)
   ->  Index Scan using client_data_pkey on client_data  (cost=0.00..56.15 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)
         Index Cond: (((macaddr)::text = 'a'::text) AND ("interval" = 56))
 Total runtime: 0.055 ms
(4 rows)

You have not shown us an example of an explicitly created index being used. — jjanes

Craig Ringer Craig Ringer · Accepted Answer · 2013-11-11T06:42:27

You should read the rest of the text after what you quoted.

PostgreSQL can only efficiently use b-tree indexes for searches that include the left-most column(s). You can use an index on (a,b) for queries that search for a or queries that look for both a and b, but not queries that search for just b. That's because of the way multi-column b-tree indexes are structured - most of the index has to be scanned anyway, so it's often more efficient for PostgreSQL to just do a full table scan.

If you need to deal with them as discrete columns and if you need to do lots of searches / fast searches on b, create a separate index on b as well.

You might find that if you SET enable_seqscan = off (use this for testing purposes only) PostgreSQL will use your index for a non-left-most column, but it'll probably be slower than a seqscan. If it isn't you need to see if your random_page_cost and seq_page_cost settings match reality.

Primary key index not getting used

1 Answers