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

Dear All,

I think periodic reindex may be the solution. Even after reducing the
autovacuum fraction to 0.05, the index still seems to bloat.

After another couple of days runtime, the index is using 11MB, and
I get a query that takes 2.448ms. Then I reindex (takes about 3 sec),
and the index falls to 16kB, and the query takes 0.035ms.

So... problem solved for me: I just have to reindex every few hours.
BUT, this suggests a few remaining things:


1. The documentation still suggests that reindex should not be needed in
"normal" operation...  is this true? Or are the docs wrong? Or have I
got such an edge case? Does this suggest that an auto-reindexer would be
a useful feature?


2. Is there any way to force the planner to use (or ignore) a specific
index, for testing purposes, short of actually dropping the index?
This would be very useful for debugging, especially given that query
plans can only really be fully tested on production systems, and that
dropping indexes is rather a bad thing to do when live operation is
simultaneously happening on that server!

Thanks again for your help.

Best wishes,

Richard





fsc_log=> explain analyse select * from tbl_tracker WHERE
parcel_id_code='32453' AND exit_state IS NULL;

QUERY PLAN
------------------------------------------------------
  Bitmap Heap Scan on tbl_tracker  (cost=20.81..23.32 rows=1 width=174)
(actual time=2.408..2.408 rows=1 loops=1)
    Recheck Cond: ((exit_state IS NULL) AND (parcel_id_code = 32453))
    ->  BitmapAnd  (cost=20.81..20.81 rows=1 width=0) (actual
time=2.403..2.403 rows=0 loops=1)
          ->  Bitmap Index Scan on tbl_tracker_exit_state_idx
(cost=0.00..9.25 rows=132 width=0) (actual time=2.378..2.378 rows=5 loops=1)
                Index Cond: (exit_state IS NULL)
          ->  Bitmap Index Scan on tbl_tracker_parcel_id_code_idx
(cost=0.00..11.30 rows=62 width=0) (actual time=0.022..0.022 rows=65
loops=1)
                Index Cond: (parcel_id_code = 32453)
  Total runtime: 2.448 ms


fsc_log => REINDEX;


fsc_log=> explain analyse select * from tbl_tracker WHERE
parcel_id_code='32453' AND exit_state IS NULL;

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.007..0.008 rows=1 loops=1)
    Index Cond: (parcel_id_code = 32453)
  Total runtime: 0.035 ms
(3 rows)