Thread: see a current query
Hello i'am logged in as superuser (pgsql) trying to SELECT * FROM pg_stat_activity; and seeing NULLs instead of current_query column& What is it?
On Wed, 2003-10-22 at 05:26, sad wrote: > Hello > > i'am logged in as superuser (pgsql) > trying to > SELECT * FROM pg_stat_activity; > and seeing NULLs instead of current_query column& You need to change the stats settings in postgresql.conf. By default the current query is not enabled as it does cause a performance loss.
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_datetimestamptz, 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
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 >
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 BerkusAglio Database SolutionsSan Francisco
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
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
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