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 10:11:26 -0500
Message-ID: <19059.1579187486@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGU4dz_tvnjJB3OEgErzt6V1PHACH0WqxwFFoMm+5bCNvjrF4g@mail.gmail.com>
References: <CAGU4dz_tvnjJB3OEgErzt6V1PHACH0WqxwFFoMm+5bCNvjrF4g@mail.gmail.com>
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
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: <19059.1579187486@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