Dear Tom,
Thanks againg for your help on this.
On 20/12/12 03:06, Tom Lane wrote:
Richard Neill <rn214@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
