Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters - Mailing list pgsql-performance

From Tom Lane
Subject Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
Date
Msg-id 19059.1579187486@sss.pgh.pa.us
Whole thread Raw
In response to Bad query plan decision when using multiple column index - postgresqluses only first column then filters  (Cosmin Prund <cprund@gmail.com>)
Responses Re: Bad query plan decision when using multiple column index -postgresql uses only first column then filters  (Michael Lewis <mlewis@entrata.com>)
Re: Bad query plan decision when using multiple column index -postgresql uses only first column then filters  (Cosmin Prund <cprund@gmail.com>)
List pgsql-performance
Cosmin Prund <cprund@gmail.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



pgsql-performance by date:

Previous
From: Cosmin Prund
Date:
Subject: Bad query plan decision when using multiple column index - postgresqluses only first column then filters
Next
From: Michael Lewis
Date:
Subject: Re: Bad query plan decision when using multiple column index -postgresql uses only first column then filters