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: 
 <CAGU4dz8BrUvdgt6c5Povgj--aQee_psbRyaYqdx_ffyUh7FBmg@mail.gmail.com>
References: 
 <CAGU4dz_tvnjJB3OEgErzt6V1PHACH0WqxwFFoMm+5bCNvjrF4g@mail.gmail.com>
 <19059.1579187486@sss.pgh.pa.us>
 <CAGU4dz8BrUvdgt6c5Povgj--aQee_psbRyaYqdx_ffyUh7FBmg@mail.gmail.com>
Comments: In-reply-to Cosmin Prund <cprund@gmail.com>
	message dated "Thu, 16 Jan 2020 19:18:24 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4833.1579198985.1@sss.pgh.pa.us>
Date: Thu, 16 Jan 2020 13:23:05 -0500
Message-ID: <4834.1579198985@sss.pgh.pa.us>
Precedence: bulk

Cosmin Prund <cprund@gmail.com> writes:
> I know it's an odd choice of plan - that's why I'm here!

Indeed.  I cannot reproduce it here on 10.11:

regression=# create table bb(f1 smallint, f2 serial, primary key(f1,f2));
CREATE TABLE
regression=# explain select * from bb where f1 = 92 and f2 in (10,11);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Only Scan using bb_pkey on bb  (cost=0.15..8.34 rows=1 width=6)
   Index Cond: ((f1 = 92) AND (f2 = ANY ('{10,11}'::integer[])))
(2 rows)

As I said before, as long as it chooses an indexscan at all, I wouldn't
expect variation in what clauses it chooses to use with the index.
So I don't see why this trivial example doesn't replicate your result.

If you try exactly the above on your database, do you get my result,
or a plan more like yours?

I wonder if you have some extension installed that's causing the
operators to be interpreted differently.

BTW, why do you have two identical indexes on the table?

> Indexes:
>     "PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId",
> "LucrareBugetDateId")
>     "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree
> ("LucrareBugetVersiuneId", "LucrareBugetDateId")

That shouldn't be affecting this either, but it seems wasteful.

			regards, tom lane