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
Date: Thu, 16 Jan 2020 13:23:05 -0500
Message-ID: <4834.1579198985@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cprund@gmail.com, 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: <4834.1579198985@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox