CODEWITHSUNDEEP
sqlpostgresql
Feng Yu
Feng Yu

Reputation: 1493

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

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 ?

Upvotes: 1

Views: 463

Answers (1)

Feng Yu
Feng Yu

Reputation: 1493

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.

Upvotes: 2

Related Questions