Skip site navigation (1) Skip section navigation (2)

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

From: "Tashuhito Kasahara" <kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4170: Rows estimation which are cast from TEXT is inaccurate.
Date: 2008-05-15 11:32:36
Message-ID: 200805151132.m4FBWaYo028791@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4170
Logged by:          Tashuhito Kasahara
Email address:      kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)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::oid, 0))
(2 rows)
============================================================================
====

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

Best regards.

Responses

pgsql-bugs by date

Next:From: alexDate: 2008-05-15 14:12:28
Subject: BUG #4172: postgres stops working after restart
Previous:From: shohorab hossainDate: 2008-05-15 11:21:30
Subject: problem in installing pgsql-8.3.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group