| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Cosmin Prund <cprund(at)gmail(dot)com> |
| Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters |
| Date: | 2020-01-16 15:11:26 |
| Message-ID: | 19059.1579187486@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Cosmin Prund <cprund(at)gmail(dot)com> writes:
> explain analyze
> select R, C, V from LBD
> where Ver = 92 and Id in (10,11)
> Index Scan using "IX_LBD_Ver_Id" on "LBD" (cost=0.56..2.37 rows=1
> width=13) (actual time=0.063..857.725 rows=2 loops=1)
> Index Cond: ("Ver" = 92)
> Filter: ("Id" = ANY ('{10,11}'::integer[]))
> Rows Removed by Filter: 1869178
> Planning time: 0.170 ms
> Execution time: 857.767 ms
> The IX_LBD_Ver_Id index is on two columns (Ver, Id) - it's not in "Ver"
> alone!
Seems like an odd choice of plan, then, but you haven't provided any
detail that would let anyone guess why it's not using the second index
column. For starters it would be good to show the exact table and
index schema (eg via \d+ in psql). Also, does explicitly ANALYZE'ing
the table change anything?
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Lewis | 2020-01-16 16:59:40 | Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters |
| Previous Message | Cosmin Prund | 2020-01-16 14:06:06 | Bad query plan decision when using multiple column index - postgresql uses only first column then filters |