Date: Sun, 21 May 2006 14:08:51 -0300 (ADT)
From: "Marc G. Fournier" <scrappy@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: "Marc G. Fournier" <scrappy@postgresql.org>, pgsql-sql@postgresql.org
Subject: Re: timestamp query doesn't use an index ... 
In-Reply-To: <5287.1148225462@sss.pgh.pa.us>
Message-ID: <20060521140506.N1114@ganymede.hub.org>
References: <20060520223704.T1114@ganymede.hub.org>
	<5287.1148225462@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Sun, 21 May 2006, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> I'm trying to figure out some way to speed up the following query:
>
>>    select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
>>      from page_schedule ps2
>>     where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
>> group by ps2.page_id, ps2.template_component_id
>
>> Is there some other way I can either write above query *or* do an index,
>> such that it will use the index?
>
> One-sided inequalities frequently *shouldn't* use an index, because
> they're retrieving too much of the table.  Are you sure this is fetching
> only a small fraction of the table?  Are you using PG 8.1 (8.1 would be
> likely to try to use a bitmap indexscan for this)?
>
> You could experiment with enable_seqscan = off to see if the planner is
> actually wrong about its choice.  If so, reducing random_page_cost might
> be the best permanent solution.

vrnprd=# select version();
                                             version 
------------------------------------------------------------------------------------------------
  PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)

vrnprd=# set enable_seqscan = off;
SET
vrnprd=# explain analyze
   select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
     from page_schedule ps2
    where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
;
                                                                      QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=3509.96..3513.50 rows=283 width=16) (actual time=839.460..839.769 rows=128 loops=1)
    ->  Bitmap Heap Scan on page_schedule ps2  (cost=573.65..2822.86 rows=91614 width=16) (actual time=149.788..505.438 rows=94798 loops=1)
          Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time zone)
          ->  Bitmap Index Scan on start_time_page_schedule  (cost=0.00..573.65 rows=91614 width=0) (actual time=127.761..127.761 rows=94798 loops=1)
                Index Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time zone)
  Total runtime: 846.604 ms
(6 rows)

vrnprd=#

And yup, it is definitely returning just 128 rows out of the 93k or so:

      110 |                   419 | 2005-10-26 13:15:00-03
      130 |                   215 | 2006-04-26 10:15:00-03
(128 rows)

And, not sure how far to reduce random_page_cost, but it was 4 ... tried 2 
and 1, and both come up with the same results ... with seqscan enabled, it 
does a seqscan :(

I suspected with the <= there wasn't going to be much I could do with 
this, but figured I'd make sure there wasn't something that I was 
overlooking :(

Thx ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org                              MSN . scrappy@hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664