Explicit timestamp conversion very slow - Mailing list pgsql-bugs

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