From: Richard Neill <rn214@richardneill.org>
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? (solved?)
Date: Mon, 24 Dec 2012 18:37:11 +0000
Message-ID: <50D8A0D7.2050005@richardneill.org> (raw)
In-Reply-To: <CAMkU=1zTU9RtyEAsAAfC5i0r6c-c=i-tVOPfF8J=Q-FgDQ31Qw@mail.gmail.com>
References: <50D22DE2.4090301@richardneill.org>
<28778.1355972790@sss.pgh.pa.us>
<50D3CAC4.3070800@richardneill.org>
<50D3D484.6080100@richardneill.org>
<CAMkU=1zTU9RtyEAsAAfC5i0r6c-c=i-tVOPfF8J=Q-FgDQ31Qw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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)
--
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? (solved?)
In-Reply-To: <50D8A0D7.2050005@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