Re: Why does the query planner use two full indexes, when a dedicated partial index exists?

Dear Tom,

Thanks againg for your help on this.

On 20/12/12 03:06, Tom Lane wrote:
> Richard Neill <rn214(at)richardneill(dot)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)