Message-ID: <50D3CAC4.3070800@richardneill.org>
Date: Fri, 21 Dec 2012 02:34:44 +0000
From: Richard Neill <rn214@richardneill.org>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: Re: Why does the query planner use two full indexes, when
	a dedicated partial index exists?
References: <50D22DE2.4090301@richardneill.org>
	<28778.1355972790@sss.pgh.pa.us>
In-Reply-To: <28778.1355972790@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Dear Tom,

Thanks againg for your help on this.

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance