From: Richard Neill <rn214@richardneill.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Date: Fri, 21 Dec 2012 02:34:44 +0000
Message-ID: <50D3CAC4.3070800@richardneill.org> (raw)
In-Reply-To: <28778.1355972790@sss.pgh.pa.us>
References: <50D22DE2.4090301@richardneill.org>
<28778.1355972790@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Dear Tom,
Thanks againg for your help on this.
On 20/12/12 03:06, Tom Lane wrote:
> Richard Neill <rn214@richardneill.org> writes:
>> The problem is, when I now run my query, the planner ignores the
>> dedicated index "tbl_tracker_performance_1_idx", and instead uses both
>> of the full indexes... resulting in a much much slower query (9ms vs
>> 0.08ms).
>
I've now installed 9.2. As you said, thanks to the change in 9.2 it
initially prefers the partial index.
BUT, after 1 cycle of inserting 500k rows, then deleting them all, then
starting to insert again, I find that the planner has reverted to the
former bad behaviour.
Reindexing only takes a couple of seconds, and restores correctness.
What's going on? Do I need to run reindex in a cron-job? I thought that
reindex wasn't "normally" needed, and that index bloat happened only
after every row had changed value hundreds of times.
Thanks,
Richard
---------------------
Here's the same session again.
[Please ignore the dreq_1_timestamp check - I mistakenly failed to
simplify it out of the query, and now that I reindexed, I can't redo the
experiment. I don't think it makes any difference.]
fsc_log=> explain analyse select * from tbl_tracker WHERE
parcel_id_code='90820' AND exit_state IS NULL AND (dreq_timestamp_1 >
timestamp '2012-12-20 13:02:36.652' - INTERVAL '36 hours');
QUERY PLAN
---------------------------------------------------------------
Bitmap Heap Scan on tbl_tracker (cost=17.35..19.86 rows=1 width=174)
(actual time=8.056..8.056 rows=0 loops=1)
Recheck Cond: ((exit_state IS NULL) AND (parcel_id_code = 90820))
Filter: (dreq_timestamp_1 > '2012-12-19 01:02:36.652'::timestamp
without time zone)
-> BitmapAnd (cost=17.35..17.35 rows=1 width=0) (actual
time=8.053..8.053 rows=0 loops=1)
-> Bitmap Index Scan on tbl_tracker_exit_state_idx
(cost=0.00..8.36 rows=151 width=0) (actual time=7.946..7.946 rows=20277
loops=1)
Index Cond: (exit_state IS NULL)
-> Bitmap Index Scan on tbl_tracker_parcel_id_code_idx
(cost=0.00..8.73 rows=58 width=0) (actual time=0.025..0.025 rows=72 loops=1)
Index Cond: (parcel_id_code = 90820)
Total runtime: 8.090 ms
(9 rows)
fsc_log=> REINDEX index tbl_tracker_performance_1_idx;
#This only took a couple of seconds to do.
fsc_log=> explain analyse select * from tbl_tracker WHERE
parcel_id_code='90820' AND exit_state IS NULL AND (dreq_timestamp_1 >
timestamp '2012-12-20 13:02:36.652' - INTERVAL '36 hours');
QUERY PLAN
---------------------------------------------------------------
Index Scan using tbl_tracker_performance_1_idx on tbl_tracker
(cost=0.00..5.27 rows=1 width=174) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (parcel_id_code = 90820)
Filter: (dreq_timestamp_1 > '2012-12-19 01:02:36.652'::timestamp
without time zone)
Total runtime: 0.047 ms
(4 rows)
--
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: rn214@richardneill.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <50D3CAC4.3070800@richardneill.org>
* 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