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

Cosmin Prund <cprund(at)gmail(dot)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