NOTICE: May not be considered a bug. More of an observation.
When specifying 'timestamp' to explicitly convert a string constant to a
timestamp, the query is more than 2000 time slower than the same query
without 'timestamp'. Output and more info below. Last two queries are
without and with the 'timestamp' specification.
No one may care, but I thought I would report it.
Thanks,
Mark Hamby
/* PG VERSION */
select version();
version
------------------------------------------------------------------
PostgreSQL 7.2.1 on sparc-sun-solaris2.7, compiled by GCC 2.95.2
(1 row)
/* THE DATA TABLE */
\d truth_units
Table "truth_units"
Column | Type | Modifiers
----------------+-----------------------------+-----------
time | timestamp without time zone |
unit_id | integer |
unit | character varying(13) |
color | character(1) |
subtype | integer |
symbol | integer |
echelon | integer |
hhq | integer |
in_combat | integer |
utm | character varying(11) |
latitude | real |
longitude | real |
direction | integer |
shooting | integer |
strength | integer |
speed | integer |
posture | integer |
radar_deployed | integer |
radar_on | integer |
Indexes: truth_units_unique
/* THE INDEX */
\d truth_units_unique
Index "truth_units_unique"
Column | Type
---------+-----------------------------
time | timestamp without time zone
unit_id | integer
unit | character varying(13)
btree
/* NUMBER OF DATA ROWS*/
select count(*) from truth_units;
count
--------
717334
(1 row)
/* NORMAL QUERY USES INDEX AND IS QUICK. */
explain analyze
select count(*)
from truth_units
where time = '2002-08-15 10:00'
;
psql:timestamp.sql:22: NOTICE: QUERY PLAN:
Aggregate (cost=13460.55..13460.55 rows=1 width=0) (actual
time=379.59..379.59 rows=1 loops=1)
-> Index Scan using truth_units_unique on truth_units
(cost=0.00..13451.58 rows=3587 width=0) (actual time=0.53..182.56 rows=3982
loops=1)
Total runtime: 380.00 msec
EXPLAIN
/* QUERY WITH EXPLICIT TIMESTAMP CONVERSION */
/* DOES NOT USE INDEX AND IS VERY SLOW. */
/* DOES ANYONE CARE? */
explain analyze
select count(*)
from truth_units
where time = timestamp '2002-08-15 10:00'
;
psql:timestamp.sql:32: NOTICE: QUERY PLAN:
Aggregate (cost=23484.98..23484.98 rows=1 width=0) (actual
time=775731.52..775731.53 rows=1 loops=1)
-> Seq Scan on truth_units (cost=0.00..23476.01 rows=3587 width=0)
(actual time=303737.26..775252.14 rows=3982 loops=1)
Total runtime: 775731.86 msec
EXPLAIN