Re: BUG #4170: Rows estimation which are cast from TEXT is inaccurate.

Hi.

Tom Lane wrote:
> "Tashuhito Kasahara" <kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)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.
Relevant issues ocurred on PostgreSQL versions 7.4 also.

8.2, 8.1 and 8.0 seemed to be accurate estimates on simple test.

============= 7.4
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE id < '2008-5-14 00:01:00'::text::timestamp;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..224.98 rows=3667 width=8) (actual time=0.026..30.987 rows=60 loops=1)
Filter: (id < ('2008-5-14 00:01:00'::text)::timestamp without time zone)
Total runtime: 31.074 ms
(3 rows)

============= 8.2
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE id < '2008-5-14 00:01:00'::text::timestamp;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..224.98 rows=55 width=8) (actual time=0.043..13.896 rows=60 loops=1)
Filter: (id < ('2008-5-14 00:01:00'::text)::timestamp without time zone)
Total runtime: 13.951 ms
(3 rows)

============= 8.1
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE id < '2008-5-14 00:01:00'::text::timestamp;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..224.98 rows=60 width=8) (actual time=0.034..15.409 rows=60 loops=1)
Filter: (id < ('2008-5-14 00:01:00'::text)::timestamp without time zone)
Total runtime: 15.464 ms
(3 rows)

============= 8.0
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE id < '2008-5-14 00:01:00'::text::timestamp;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..224.98 rows=63 width=8) (actual time=0.041..18.645 rows=60 loops=1)
Filter: (id < ('2008-5-14 00:01:00'::text)::timestamp without time zone)
Total runtime: 18.706 ms
(3 rows)
=============

Best regards.

--
Tatsuhito Kasahara
kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp