From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Richard Neill <rn214@richardneill.org>
Cc: Sergey Konoplev <gray.ru@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Date: Thu, 20 Dec 2012 10:49:03 -0500
Message-ID: <12105.1356018543@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1wYVjf63j+4ttZrVMmYfst7Q7ngez5e6X4s+keYbpcwdg@mail.gmail.com>
References: <50D22DE2.4090301@richardneill.org>
<CAL_0b1s6_YZDdzt3NizwC8X+dYA37T-4aZOTzw3KJ3-iXuSjjw@mail.gmail.com>
<50D2528E.7000800@richardneill.org>
<CAL_0b1t5_h-VUE+sL2cfd1E78xYOq9T7P3n31iZhLT3vEa=mOA@mail.gmail.com>
<50D25A59.3060105@richardneill.org>
<CAMkU=1wYVjf63j+4ttZrVMmYfst7Q7ngez5e6X4s+keYbpcwdg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Jeff Janes <jeff.janes@gmail.com> writes:
> In any case, I can't get it to prefer the full index in 9.1.6 at all. The
> partial index wins hands down unless the table is physically clustered by
> the parcel_id_code column. In which that case, the partial index wins by
> only a little bit.
> This is what I did for the table:
> create table tbl_tracker as select case when random()<0.001 then 2 else
> case when random()< 0.00003 then NULL else 1 end end as exit_state,
> (random()*99999)::int as parcel_id_code from generate_series(1,5000000) ;
What I did to try to duplicate Richard's situation was to create a test
table in which all the exit_state values were NULL, then build the
index, then UPDATE all but a small random fraction of the rows to 1,
then vacuum. This results in a rather bloated partial index, but I
think that's probably what he's got given that every record initially
enters the table with NULL exit_state. It would take extremely frequent
vacuuming to keep the partial index from accumulating a lot of dead
entries.
In this scenario, with 9.1, I got overly large estimates for the cost of
using the partial index; which matches up with his reports.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, jeff.janes@gmail.com, rn214@richardneill.org, gray.ru@gmail.com
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <12105.1356018543@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