From: | "David Olbersen" <DOlbersen(at)stbernard(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Type casting and indexes |
Date: | 2003-05-08 15:36:27 |
Message-ID: | E7E213858379814A9AE48CA6754F5ECB0D6DDE@mail01.stbernard.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-05-08 16:13:29 | Re: Type casting and indexes |
Previous Message | johnnnnnn | 2003-05-08 14:47:38 | Re: [PERFORM] [SQL] Unanswered Questions WAS: An unresolved performance problem. |