From: Richard Neill <rn214@richardneill.org>
To: Sergey Konoplev <gray.ru@gmail.com>
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?
Date: Thu, 20 Dec 2012 00:22:49 +0000
Message-ID: <50D25A59.3060105@richardneill.org> (raw)
In-Reply-To: <CAL_0b1t5_h-VUE+sL2cfd1E78xYOq9T7P3n31iZhLT3vEa=mOA@mail.gmail.com>
References: <50D22DE2.4090301@richardneill.org>
<CAL_0b1s6_YZDdzt3NizwC8X+dYA37T-4aZOTzw3KJ3-iXuSjjw@mail.gmail.com>
<50D2528E.7000800@richardneill.org>
<CAL_0b1t5_h-VUE+sL2cfd1E78xYOq9T7P3n31iZhLT3vEa=mOA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Thanks for your help,
On 20/12/12 00:08, Sergey Konoplev wrote:
> On Wed, Dec 19, 2012 at 3:49 PM, Richard Neill <rn214@richardneill.org> wrote:
>> * The reindex solution doesn't work. I just tried it, and the query planner
>> is still using the wrong indexes.
>
> Can you show the explain analyze with tbl_tracker_performance_1_idx
> straight after reindex (eg. before it has been bloated again)?
Sure. Just done it now... the system has been fairly lightly loaded for
the last few hours - though I did have to change the specific number of
the parcel_id_code in the query.
fsc_log=> explain analyse select * from tbl_tracker where
parcel_id_code=92223 and exit_state is null;
QUERY PLAN
-----------------------------------------------------------
Index Scan using tbl_tracker_exit_state_idx on tbl_tracker
(cost=0.00..6.34 rows=1 width=174) (actual time=0.321..1.871 rows=1 loops=1)
Index Cond: (exit_state IS NULL)
Filter: (parcel_id_code = 92223)
Total runtime: 1.905 ms
(4 rows)
And now, force it, by dropping the other index (temporarily):
fsc_log=> drop index tbl_tracker_exit_state_idx;
DROP INDEX
fsc_log=> explain analyse select * from tbl_tracker where
parcel_id_code=92223 and exit_state is null;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using tbl_tracker_performance_1_idx on tbl_tracker
(cost=0.00..7.78 rows=1 width=174) (actual time=0.040..0.041 rows=1 loops=1)
Index Cond: (parcel_id_code = 92223)
Total runtime: 0.077 ms
(3 rows)
As far as I can tell, the query planner really is just getting it wrong.
BTW, there is a significant effect on speed caused by running the same
query twice (it pulls stuff from disk into the OS disk-cache), but I've
already accounted for this.
Richard
--
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, gray.ru@gmail.com
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <50D25A59.3060105@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