pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Type casting and indexes
4+ messages / 3 participants
[nested] [flat]

* Type casting and indexes
@ 2003-05-08 15:36 David Olbersen <DOlbersen@stbernard.com>
  2003-05-08 16:13 ` Re: Type casting and indexes Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2003-05-08 16:25 ` Re: Type casting and indexes Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: David Olbersen @ 2003-05-08 15:36 UTC (permalink / raw)
  To: pgsql-performance

I hope this hasn't been answered before, I've looked at the docs here:
  http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=index.html

Anyway, I've found a (bug|feature|standard?) with type casting and index usage.

I've got a table with a column that's a timestamp with time zone. This column is indexed. If I issue the "normal" query of: 

    SELECT count(*) FROM foo WHERE bar > '2003-05-05':;timestamp

I get the following EXPLAIN ANALYZE output:

urldb=> explain select count(*) from foo where bar > '2003-05-05'::timestamp;
                             QUERY PLAN                     
------------------------------------------------------------------------
 Aggregate  (cost=89960.75..89960.75 rows=1 width=0) (actual time=
             56706.58..56706.58 rows=1 loops=1)
   ->  Seq Scan on urlinfo  (cost=0.00..87229.45 rows=1092521 width=0) (actual 
        time=25.37..56537.86 rows=27490 loops=1)
         Filter: (ratedon > ('2003-05-05 00:00:00'::timestamp without time 
                  zone)::timestamp with time zone)
 Total runtime: 56706.67 msec

So it seems that the type conversion is killing the use of the index, even though the type conversion has to happen for the condition to be tested.

If I change this query slightly, by casting to timestamptz, I get the following EXPLAIN ANALYZE output:

                              QUERY PLAN                               
-------------------------------------------------------------------------
 Aggregate  (cost=38609.70..38609.70 rows=1 width=0) (actual time=547.58..547.58 
             rows=1 loops=1)
   ->  Index Scan using urlinfo_on on urlinfo  (cost=0.00..38578.97 rows=12295 
        width=0) (actual time=0.18..381.95 rows=27490 loops=1)
         Index Cond: (ratedon > '2003-05-05 00:00:00-07'::timestamp with time 
                      zone)
 Total runtime: 548.17 msec

That's much better! Is this the way it's supposed to work?

--------------------------
David Olbersen 
iGuard Engineer
11415 West Bernardo Court 
San Diego, CA 92127 
1-858-676-2277 x2152




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Type casting and indexes
  2003-05-08 15:36 Type casting and indexes David Olbersen <DOlbersen@stbernard.com>
@ 2003-05-08 16:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2003-05-08 22:35   ` Re: Type casting and indexes Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 4+ messages in thread

From: Stephan Szabo @ 2003-05-08 16:13 UTC (permalink / raw)
  To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-performance

On Thu, 8 May 2003, David Olbersen wrote:

> Anyway, I've found a (bug|feature|standard?) with type casting and index usage.
>
> I've got a table with a column that's a timestamp with time zone. This
> column is indexed. If I issue the "normal" query of:
>
>     SELECT count(*) FROM foo WHERE bar > '2003-05-05':;timestamp
>
> I get the following EXPLAIN ANALYZE output:
>
> urldb=> explain select count(*) from foo where bar > '2003-05-05'::timestamp;
>                              QUERY PLAN
> ------------------------------------------------------------------------
>  Aggregate  (cost=89960.75..89960.75 rows=1 width=0) (actual time=
>              56706.58..56706.58 rows=1 loops=1)
>    ->  Seq Scan on urlinfo  (cost=0.00..87229.45 rows=1092521 width=0) (actual
>         time=25.37..56537.86 rows=27490 loops=1)
>          Filter: (ratedon > ('2003-05-05 00:00:00'::timestamp without time
>                   zone)::timestamp with time zone)
>  Total runtime: 56706.67 msec
>
> So it seems that the type conversion is killing the use of the index,
> even though the type conversion has to happen for the condition to be
> tested.

IIRC, timestamp->timestamptz is not considered to give a constant value
(ie, is not stable) probably since it depends on timezone settings which
could be changed (for example by a function) during the query, so for each
row the conversion from '2003-05-05 00:00:00'::timestamp without time zone
to a timestamp with time zone can potentially give a different answer.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Type casting and indexes
  2003-05-08 15:36 Type casting and indexes David Olbersen <DOlbersen@stbernard.com>
  2003-05-08 16:13 ` Re: Type casting and indexes Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-08 22:35   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2003-05-08 22:35 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: David Olbersen <DOlbersen@stbernard.com>; pgsql-performance

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Thu, 8 May 2003, David Olbersen wrote:
>> So it seems that the type conversion is killing the use of the index,
>> even though the type conversion has to happen for the condition to be
>> tested.

> IIRC, timestamp->timestamptz is not considered to give a constant value
> (ie, is not stable)

No: it is stable, but not immutable, because it depends on SET TIMEZONE.
(Our policy on those is if you change one mid-query, it's unspecified
whether the query will notice or not.)  So the query is potentially
indexable.

The problem here is that instead of seeing a constant, the planner sees
a nonconstant function invocation on the right side of '>', and so it
has to fall back to a default selectivity estimate instead of being able
to extract a reasonable estimate from pg_statistic.  The default
estimate is high enough to discourage an indexscan ...

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Type casting and indexes
  2003-05-08 15:36 Type casting and indexes David Olbersen <DOlbersen@stbernard.com>
@ 2003-05-08 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2003-05-08 16:25 UTC (permalink / raw)
  To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-performance

"David Olbersen" <DOlbersen@stbernard.com> writes:
> So it seems that the type conversion is killing the use of the index, even though the type conversion has to happen for the condition to be tested.

Seems like I just answered this yesterday ;-)

Note the difference in the number of estimated rows in the two explains.
The reason is that the timestamptz conversion is not a constant and so
the planner can't get a good estimate of the number of rows that will
satisfy it.  (And the reason it's not a constant is that it depends on
SET TIMEZONE.)

Bottom line: declare the constant correctly.  Or at least don't
gratuitously cast it to the wrong thing.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-05-08 22:35 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-08 15:36 Type casting and indexes David Olbersen <DOlbersen@stbernard.com>
2003-05-08 16:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-08 22:35   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-08 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>

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