Why PostgreSQL multi-column index not working for first column?

Question

I'm using PostgreSQL 14. I'm confused why multi-column index not working for first column.

Here is the output:

# \d+ para_data
...

    "para_data_pkey" PRIMARY KEY, btree (id)
    "para_data_eq_id_timestamp_high_idx" btree (eq_id, timestamp_high)

You can see when query eq_id and timestamp_high at one time is using index:

# explain analyze verbose SELECT "ch00_status", "ch00_wf_peak_peak" FROM para_data WHERE eq_id = '020090120479F281' and timestamp_high > 1 and timestamp_high < 100;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using para_data_eq_id_timestamp_high_idx on public.para_data  (cost=0.42..8.44 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)
   Output: ch00_status, ch00_wf_peak_peak
   Index Cond: (((para_data.eq_id)::text = '020090120479F281'::text) AND (para_data.timestamp_high > 1) AND (para_data.timestamp_high < 100))
 Planning Time: 0.064 ms
 Execution Time: 0.018 ms

But when I just query for eq_id, it's not working:

# explain analyze verbose SELECT "ch00_status", "ch00_wf_peak_peak" FROM para_data WHERE eq_id = '020090120479F281';
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.para_data  (cost=0.00..24496.89 rows=53693 width=16) (actual time=12.849..47.731 rows=52348 loops=1)
   Output: ch00_status, ch00_wf_peak_peak
   Filter: ((para_data.eq_id)::text = '020090120479F281'::text)
   Rows Removed by Filter: 122283
 Planning Time: 0.049 ms
 Execution Time: 49.490 ms

Am I misunderstanding the multi-column index ?

It seems the DBMS doesn't see it appropriate to use the index. Yes, you can easily find all eq_id = '020090120479F281' rows in th index, because the index starts with this ID. You have understood this correctly. But maybe the DBMS fears it may get so many rows for one ID that it's better to simply read the table sequentially. After all, an index is just an offer to the DBMS. It can use it or decide not to use it. — Thorsten Kettner
– Thorsten Kettner, Commented Jan 21, 2022 at 9:53
Yes you're right ! Seems this eq_id has so many records and postgresql does not use index. I try to use another eq_id to query, and multi-column index is working. Thanks. — Feng Yu
– Feng Yu, Commented Jan 21, 2022 at 9:58
Your second query fetches about 30% of the rows in the table. A Seq Scan is more efficient in that case. — user330315
– user330315, Commented Jan 21, 2022 at 10:00
From the manual : "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns". You can force the usage of the index for test purpose by turning off enable_seqscan see the manual — Edouard
– Edouard, Commented Jan 21, 2022 at 10:02

Feng Yu · Accepted Answer · 2022-01-21 10:13:12Z

Thanks to @thorsten-kettner

If fetched records so many rows, will not use index.

I try to use another eq_id with small result sets and index is working:

# select count(*), eq_id from para_data group by eq_id;
 count |      eq_id       
-------+------------------
 52501 | 0100C0130479A5F1
 52348 | 020090120479F281
 52614 | 020280240479A5F1
   559 | 070030210481AEF9
  7013 | 0F0180340479F349
  3910 | 130230120479F281
   229 | 1500C0280479F349
  2014 | 150100340479F349
    86 | 150270330479F349
  3357 | 1600B02E0479F349

# explain analyze verbose SELECT "ch00_status", "ch00_wf_peak_peak" FROM para_data WHERE eq_id = '070030210481AEF9';
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using para_data_eq_id_timestamp_high_idx on public.para_data  (cost=0.42..1146.48 rows=588 width=16) (actual time=0.027..0.325 rows=559 loops=1)
   Output: ch00_status, ch00_wf_peak_peak
   Index Cond: ((para_data.eq_id)::text = '070030210481AEF9'::text)
 Planning Time: 0.049 ms
 Execution Time: 0.350 ms

Thanks to you all.

Collectives™ on Stack Overflow

Why PostgreSQL multi-column index not working for first column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related