The following bug has been logged online:

Bug reference: 4170
Logged by: Tashuhito Kasahara
Email address: kasahara.tatsuhito@oss.ntt.co.jp
PostgreSQL version: 8.3.1
Operating system: Linux
Description: Rows estimation which are cast from TEXT is inaccurate.
Details:

I noticed that rows estimation is not accurate when we cast some datetype to
TEXT.
See the following example. (TEXT -> TIMESTAMP)

================================================== ==========================
====
test=# SELECT count(*) FROM test WHERE t < '2008-05-14 23:55:00';
count
-------
86099
(1 row)

test=# EXPLAIN SELECT * FROM test WHERE t < '2008-05-14 23:55:00';
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test (cost=0.00..1727.00 rows=85721 width=12)
Filter: (t < '2008-05-14 23:55:00'::timestamp without time zone)
(2 rows)


test=# EXPLAIN SELECT * FROM test WHERE t < '2008-05-14
23:55:00'::text::timestamp;
QUERY PLAN
----------------------------------------------------------------------------

Seq Scan on test (cost=0.00..2209.00 rows=32133 width=12) <- too little
number of the estimates
Filter: (t < ('2008-05-14 23:55:00'::text)::timestamp without time zone)
(2 rows)

test=# SELECT count(*) FROM test WHERE t < '2008-05-14 23:55:00';
count
-------
86099
(1 row)
================================================== ==========================
====

We can avoid this problem by setting appropriate cast-function.

================================================== ==========================
====
CREATE FUNCTION text2timestamp(text) RETURNS timestamp AS
$$
SELECT timestamp_in(textout($1), 0, 0);
$$
LANGUAGE sql STRICT STABLE;

CREATE CAST (text AS timestamp) WITH FUNCTION text2timestamp(text) AS
ASSIGNMENT;

test=# EXPLAIN SELECT * FROM test WHERE t < '2008-05-14
23:55:00'::text::timestamp;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1968.00 rows=85721 width=12)
Filter: (t < timestamp_in('2008-05-14 23:55:00'::cstring, 0:id, 0))
(2 rows)
================================================== ==========================
====

I think it's a bug and will be troubled at plan optimization.

Best regards.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Tashuhito Kasahara" <kasahara.tatsuhito@oss.ntt.co.jp> writes:
> test=# EXPLAIN SELECT * FROM test WHERE t < '2008-05-14
> 23:55:00'::text::timestamp;
> QUERY PLAN
> ----------------------------------------------------------------------------

> Seq Scan on test (cost=0.00..2209.00 rows=32133 width=12) <- too little
> number of the estimates
> Filter: (t < ('2008-05-14 23:55:00'::text)::timestamp without time zone)
> (2 rows)

Hmm ... as of 8.3 this will generate a CoerceViaIO node, and it looks
like I forgot to teach eval_const_expressions how to simplify those.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs