From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cosmin Prund <cprund@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad query plan decision when using multiple column index -
 postgresql uses only first column then filters
In-reply-to: 
 <CAGU4dz_tvnjJB3OEgErzt6V1PHACH0WqxwFFoMm+5bCNvjrF4g@mail.gmail.com>
References: 
 <CAGU4dz_tvnjJB3OEgErzt6V1PHACH0WqxwFFoMm+5bCNvjrF4g@mail.gmail.com>
Comments: In-reply-to Cosmin Prund <cprund@gmail.com>
	message dated "Thu, 16 Jan 2020 16:06:06 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19058.1579187486.1@sss.pgh.pa.us>
Date: Thu, 16 Jan 2020 10:11:26 -0500
Message-ID: <19059.1579187486@sss.pgh.pa.us>
Precedence: bulk

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