pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planner: current_* vs. explicit date
6+ messages / 4 participants
[nested] [flat]
* Query planner: current_* vs. explicit date
@ 2003-10-22 13:25 Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` Re: Query planner: current_* vs. explicit date scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Gamache @ 2003-10-22 13:25 UTC (permalink / raw)
To: pgsql-sql
PsotgreSQL 7.2.4:
Query planner is behaving strangely. It operates differently for explicit dates
and derived dates... any ideas on why? ( and why I might not have noticed this
before... )
CREATE TABLE trans_table (
id serial,
user_name varchar(50),
trans_type varchar(50),
trans_data varchar(50),
trans_date timestamptz,
trans_uuid uniqueidentifier,
CONSTRAINT trans_table_pkey PRIMARY KEY (id)
) WITH OIDS;
... Insert lots of data ...
CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date);
CREATE INDEX trans_table_user_date_idx ON trans_table USING btree
(user_name,trans_date);
CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid);
VACUUM ANALYZE trans_table;
EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp;
Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4)
EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00
AM'::timestamp;
Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69
rows=417 width=4)
CG
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query planner: current_* vs. explicit date
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
@ 2003-10-22 19:12 ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-22 20:25 ` Re: Query planner: current_* vs. explicit date Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: scott.marlowe @ 2003-10-22 19:12 UTC (permalink / raw)
To: Chris Gamache <cgg007@yahoo.com>; +Cc: pgsql-sql
I'd guess that the planner doesn't know what current_date::timestamp is
ahead of time, so it chooses a seq scan.
On Wed, 22 Oct 2003, Chris Gamache wrote:
> PsotgreSQL 7.2.4:
>
> Query planner is behaving strangely. It operates differently for explicit dates
> and derived dates... any ideas on why? ( and why I might not have noticed this
> before... )
>
> CREATE TABLE trans_table (
> id serial,
> user_name varchar(50),
> trans_type varchar(50),
> trans_data varchar(50),
> trans_date timestamptz,
> trans_uuid uniqueidentifier,
> CONSTRAINT trans_table_pkey PRIMARY KEY (id)
> ) WITH OIDS;
>
> ... Insert lots of data ...
>
> CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date);
> CREATE INDEX trans_table_user_date_idx ON trans_table USING btree
> (user_name,trans_date);
> CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid);
>
> VACUUM ANALYZE trans_table;
>
> EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp;
> Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4)
>
> EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00
> AM'::timestamp;
> Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69
> rows=417 width=4)
>
> CG
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query planner: current_* vs. explicit date
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` Re: Query planner: current_* vs. explicit date scott.marlowe <scott.marlowe@ihs.com>
@ 2003-10-22 20:25 ` Josh Berkus <josh@agliodbs.com>
2003-10-22 22:45 ` Re: Query planner: current_* vs. explicit date Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2003-10-22 20:25 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; Chris Gamache <cgg007@yahoo.com>; +Cc: pgsql-sql
Scott, Chris,
> I'd guess that the planner doesn't know what current_date::timestamp is
> ahead of time, so it chooses a seq scan.
Yes, this is a known problem. There was a single-query workaround, but I
can't remember it right now.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query planner: current_* vs. explicit date
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` Re: Query planner: current_* vs. explicit date scott.marlowe <scott.marlowe@ihs.com>
2003-10-22 20:25 ` Re: Query planner: current_* vs. explicit date Josh Berkus <josh@agliodbs.com>
@ 2003-10-22 22:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-23 15:29 ` Re: Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-10-22 22:45 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: scott.marlowe <scott.marlowe@ihs.com>; Chris Gamache <cgg007@yahoo.com>; pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
>> I'd guess that the planner doesn't know what current_date::timestamp is
>> ahead of time, so it chooses a seq scan.
> Yes, this is a known problem. There was a single-query workaround, but I
> can't remember it right now.
Right; the problem from the planner's point of view is that it sees
WHERE datecol >= unknownvalue
and it has no idea how much of the table will be fetched by this query.
Its default guess is that a fair fraction of the table will be fetched
(one-third, I think) and that dissuades it from using an indexscan.
This seems reasonable to me, since the worst-case behavior if it were
to default to an indexscan could be awful.
The easiest workaround is to convert the query into a range query,
for example
WHERE datecol >= current_date AND datecol <= current_date + 1000;
being careful that both comparison values are nonconstant (don't use
'infinity'::timestamp, for instance, even though that might seem like
a reasonable thing to do). The planner still has no idea how many rows
will be fetched exactly, but it does realize that this is a range
condition, and its default assumption about the number of matching rows
is small enough to encourage indexscan use.
Of course this workaround assumes that you can pick an upper bound that
you are sure is past the end of the available values, but that's usually
not hard in the sort of context where you would have thought that the
one-sided inequality test is a sane thing to do anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query planner: current_* vs. explicit date
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` Re: Query planner: current_* vs. explicit date scott.marlowe <scott.marlowe@ihs.com>
2003-10-22 20:25 ` Re: Query planner: current_* vs. explicit date Josh Berkus <josh@agliodbs.com>
2003-10-22 22:45 ` Re: Query planner: current_* vs. explicit date Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-10-23 15:29 ` Chris Gamache <cgg007@yahoo.com>
2003-10-27 16:05 ` Re: Query planner: current_* vs. explicit date Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Chris Gamache @ 2003-10-23 15:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Thanks Tom (and others!) Right-on-the-money, as always...
By giving it a definitive range I was able to coax query planner to use the
index:
SELECT id FROM trans_table WHERE trans_date >= (SELECT
current_date::timestamptz) AND trans_date < (SELECT current_timestamp);
gave me from midnight to the present...
Aside from a slight amount of ugliness, the solution is quite adequate. The
subselects shouldn't cause too much overhead, yes?
BTW, This didn't work:
SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND
trans_date < current_timestamp;
Which was a "nonconstant" version of the above. I think it still suffers from
the timestamp >= unknown_value problem.
CG
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> being careful that both comparison values are nonconstant (don't use
> 'infinity'::timestamp, for instance, even though that might seem like
> a reasonable thing to do). The planner still has no idea how many rows
> will be fetched exactly, but it does realize that this is a range
> condition, and its default assumption about the number of matching rows
> is small enough to encourage indexscan use.
>
> Of course this workaround assumes that you can pick an upper bound that
> you are sure is past the end of the available values, but that's usually
> not hard in the sort of context where you would have thought that the
> one-sided inequality test is a sane thing to do anyway.
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query planner: current_* vs. explicit date
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` Re: Query planner: current_* vs. explicit date scott.marlowe <scott.marlowe@ihs.com>
2003-10-22 20:25 ` Re: Query planner: current_* vs. explicit date Josh Berkus <josh@agliodbs.com>
2003-10-22 22:45 ` Re: Query planner: current_* vs. explicit date Tom Lane <tgl@sss.pgh.pa.us>
2003-10-23 15:29 ` Re: Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
@ 2003-10-27 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-10-27 16:05 UTC (permalink / raw)
To: cgg007@yahoo.com; +Cc: pgsql-sql
Chris Gamache <cgg007@yahoo.com> writes:
> By giving it a definitive range I was able to coax query planner to use the
> index:
> SELECT id FROM trans_table WHERE trans_date >= (SELECT
> current_date::timestamptz) AND trans_date < (SELECT current_timestamp);
> BTW, This didn't work:
> SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND
> trans_date < current_timestamp;
[ scratches head... ] AFAICS the latter should "work" too. Doesn't
EXPLAIN show the same estimated row count for both versions?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-10-27 16:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-22 13:25 Query planner: current_* vs. explicit date Chris Gamache <cgg007@yahoo.com>
2003-10-22 19:12 ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-22 20:25 ` Josh Berkus <josh@agliodbs.com>
2003-10-22 22:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-23 15:29 ` Chris Gamache <cgg007@yahoo.com>
2003-10-27 16:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox