[SQL] Incomprehensible dogged sort in Merge Join
Hello all,
Trying to get an extra time savings in my query, I stopped at an unusual
doggedness of the planner.
Here is the query:
>-<
select *
from (
select *
from "user_history"
order by name
) as uh
right join log_example_3 as log
on log.name = uh.name
>-<
And that is its plan (attached one is the same, but with costs):
>-<
Merge Left Join
Merge Cond: ("outer".name = "inner".name)
-> Sort
Sort Key: log.name
-> Seq Scan on log_example_3 log
-> Sort
Sort Key: uh.name
-> Subquery Scan uh
-> Sort
Sort Key: name
-> Seq Scan on user_history
>-<
The strange thing is that planner can combine two sorts by uh.name key
in one, but it seems it can't see this.
May be this can be recorded as a needed feature for future releases?
Here is a code for two tables that I have in the query:
>-<
create table user_history (
rec_id SERIAL not null,
date TIMESTAMPnot null,
action INT2 not null,
uid INT4 not null,
name CHAR(10) null default NULL,
constraint PK_USER_HISTORY primary key (rec_id),
constraint AK_DATE_USER_HIS unique (date)
);
create table log_example_3 (
rec_id integer not null,
datetimestamp not null,
uid integer not null,
namechar(10) not null,
constraint PK_log_example_3 primary key (rec_id)
);
>-<
With best regards to all of you,
Aleksandr.
ÿþ M e r g e L e f t J o i n
( c o s t = 2 0 7 6 4 6 . 0 0 . . 2 1 3 8 6 4 . 1 2 r o w s = 3 4 7 8 5 1
w i d t h = 6 2 ) ( a c t u a l
t i m e = 3 0 9 2 2 . 3 6 6 . . 3 2 1 6 6 . 5 1 8 r o w s = 1 3 9 0 8
l o o p s = 1 )
M e r g e C o n d : ( " o u t e r " . n a m e =
" i n n e r " . n a m e )
- > S o r t ( c o s t = 6 . 7 1 . . 7 . 0 3 r o w s = 1 2 7
w i d t h = 3 0 ) ( a c t u a l t i m e = 1 . 1 1 7 . . 1 . 2 0 7
r o w s = 1 0 0 l o o p s = 1 )
S o r t K e y : l o g . n a m e
- > S e q S c a n o n l o g _ e x a m p l e _ 3
l o g ( c o s t = 0 . 0 0 . . 2 . 2 7 r o w s = 1 2 7
w i d t h = 3 0 ) ( a c t u a l t i m e = 0 . 0 5 3 . . 0 . 1 7 7
r o w s = 1 0 0 l o o p s = 1 )
- > S o r t ( c o s t = 2 0 7 6 3 9 . 2 9 . . 2 0 9 0 0 8 . 7 8
r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l
t i m e = 3 0 9 2 1 . 1 7 1 . . 3 1 4 6 7 . 1 1 7 r o w s = 4 4 2 8 4 8
l o o p s = 1 )
S o r t K e y : u h . n a m e
- > S u b q u e r y S c a n u h
( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 8 2 9 5 . 3 5 r o w s = 5 4 7 7 9 6
w i d t h = 3 2 ) ( a c t u a l
t i m e = 1 9 2 7 7 . 9 6 3 . . 2 1 5 9 5 . 8 7 4 r o w s = 5 4 7 7 9 6
l o o p s = 1 )
- > S o r t
( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 2 8 1 7 . 3 9 r o w s = 5 4 7 7 9 6
w i d t h = 3 2 ) ( a c t u a l
t i m e = 1 9 2 7 7 . 9 0 8 . . 2 0 1 0 4 . 5 6 8 r o w s = 5 4 7 7 9 6
l o o p s = 1 )
S o r t K e y : n a m e
- > S e q S c a n o n
u s e r _ h i s t o r y ( c o s t = 0 . 0 0 . . 2 2 1 0 3 . 9 6
r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l
t i m e = 0 . 0 5 1 . . 1 4 7 4 . 1 4 3 r o w s = 5 4 7 7 9 6
l o o p s = 1 )
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Incomprehensible dogged sort in Merge Join
Aleksandr Vinokurov <[EMAIL PROTECTED]> writes:
> Trying to get an extra time savings in my query, I stopped at an unusual
> doggedness of the planner.
> Merge Left Join
> Merge Cond: ("outer".name = "inner".name)
> -> Sort
> Sort Key: log.name
> -> Seq Scan on log_example_3 log
> -> Sort
> Sort Key: uh.name
> -> Subquery Scan uh
> -> Sort
> Sort Key: name
> -> Seq Scan on user_history
> The strange thing is that planner can combine two sorts by uh.name key
> in one, but it seems it can't see this.
> May be this can be recorded as a needed feature for future releases?
When criticizing planner deficiencies, it's considered polite to use
something that's less than two major releases back ;-)
CVS HEAD gets this right, although I need to go look at why it's
sticking a Materialize in there:
regression=# explain select * from (select * from tenk1 order by twothousand)
uh right join tenk1 log on log.thousand = uh.twothousand;
QUERY PLAN
---
Merge Right Join (cost=4575.77..6225.77 rows=10 width=488)
Merge Cond: (tenk1.twothousand = log.thousand)
-> Sort (cost=2287.89..2312.89 rows=1 width=244)
Sort Key: tenk1.twothousand
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244)
-> Materialize (cost=2287.89..2412.89 rows=1 width=244)
-> Sort (cost=2287.89..2312.89 rows=1 width=244)
Sort Key: log.thousand
-> Seq Scan on tenk1 log (cost=0.00..458.00 rows=1
width=244)
(9 rows)
[ pokes at it a bit more... ] 8.1 and 8.2 get it right for a plain
join, but not for an outer join. Strange, I need to check that too.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Incomprehensible dogged sort in Merge Join
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes:
> And that is its plan (attached one is the same, but with costs):
>
>>-<
> Merge Left Join
>Merge Cond: ("outer".name = "inner".name)
>-> Sort
> Sort Key: log.name
> -> Seq Scan on log_example_3 log
>-> Sort
> Sort Key: uh.name
> -> Subquery Scan uh
>-> Sort
> Sort Key: name
> -> Seq Scan on user_history
>>-<
What version are you running with?
Incidentally with CVS HEAD I see it working:
postgres=# explain select * from (select * from a order by i) as a right join b
on a.i = b.i ;
QUERY PLAN
-
Merge Right Join (cost=299.56..675.13 rows=22898 width=16)
Merge Cond: (public.a.i = b.i)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: public.a.i
-> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: b.i
-> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)
> With best regards to all of you,
> Aleksandr.
> ÿþ M e r g e L e f t J o i n
> ( c o s t = 2 0 7 6 4 6 . 0 0 . . 2 1 3 8 6 4 . 1 2 r o w s = 3 4 7 8 5 1
> w i d t h = 6 2 ) ( a c t u a l
> t i m e = 3 0 9 2 2 . 3 6 6 . . 3 2 1 6 6 . 5 1 8 r o w s = 1 3 9 0 8
> l o o p s = 1 )
> M e r g e C o n d : ( " o u t e r " . n a m e =
> " i n n e r " . n a m e )
> - > S o r t ( c o s t = 6 . 7 1 . . 7 . 0 3 r o w s = 1 2 7
> w i d t h = 3 0 ) ( a c t u a l t i m e = 1 . 1 1 7 . . 1 . 2 0 7
> r o w s = 1 0 0 l o o p s = 1 )
> S o r t K e y : l o g . n a m e
> - > S e q S c a n o n l o g _ e x a m p l e _ 3
> l o g ( c o s t = 0 . 0 0 . . 2 . 2 7 r o w s = 1 2 7
> w i d t h = 3 0 ) ( a c t u a l t i m e = 0 . 0 5 3 . . 0 . 1 7 7
> r o w s = 1 0 0 l o o p s = 1 )
> - > S o r t
> ( c o s t = 2 0 7 6 3 9 . 2 9 . . 2 0 9 0 0 8 . 7 8 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 3 0 9 2 1 . 1 7 1 . . 3 1 4 6 7 . 1 1 7 r o w s = 4 4 2 8 4 8
> l o o p s = 1 )
> S o r t K e y : u h . n a m e
> - > S u b q u e r y S c a n u h
> ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 8 2 9 5 . 3 5 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 1 9 2 7 7 . 9 6 3 . . 2 1 5 9 5 . 8 7 4 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
> - > S o r t
> ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 2 8 1 7 . 3 9 r o w s = 5 4 7 7 9 6
> w i d t h = 3 2 ) ( a c t u a l
> t i m e = 1 9 2 7 7 . 9 0 8 . . 2 0 1 0 4 . 5 6 8 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
> S o r t K e y : n a m e
> - > S e q S c a n o n
> u s e r _ h i s t o r y ( c o s t = 0 . 0 0 . . 2 2 1 0 3 . 9 6
> r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l
> t i m e = 0 . 0 5 1 . . 1 4 7 4 . 1 4 3 r o w s = 5 4 7 7 9 6
> l o o p s = 1 )
gesundheit.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Incomprehensible dogged sort in Merge Join
Gregory Stark wrote:
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes:
And that is its plan (attached one is the same, but with costs):
-<
Merge Left Join
Merge Cond: ("outer".name = "inner".name)
-> Sort
Sort Key: log.name
-> Seq Scan on log_example_3 log
-> Sort
Sort Key: uh.name
-> Subquery Scan uh
-> Sort
Sort Key: name
-> Seq Scan on user_history
-<
What version are you running with?
8.0.1, sorry for missing.
Incidentally with CVS HEAD I see it working:
postgres=# explain select * from (select * from a order by i) as a right join b
on a.i = b.i ;
QUERY PLAN
-
Merge Right Join (cost=299.56..675.13 rows=22898 width=16)
Merge Cond: (public.a.i = b.i)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: public.a.i
-> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
-> Sort (cost=149.78..155.13 rows=2140 width=8)
Sort Key: b.i
-> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)
With best regards to all of you,
Aleksandr.
ÿþ
gesundheit.
Best wishes,
Aleksandr.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Incomprehensible dogged sort in Merge Join
Tom Lane wrote:
Aleksandr Vinokurov <[EMAIL PROTECTED]> writes:
Trying to get an extra time savings in my query, I stopped at an unusual
doggedness of the planner.
Merge Left Join
Merge Cond: ("outer".name = "inner".name)
-> Sort
Sort Key: log.name
-> Seq Scan on log_example_3 log
-> Sort
Sort Key: uh.name
-> Subquery Scan uh
-> Sort
Sort Key: name
-> Seq Scan on user_history
The strange thing is that planner can combine two sorts by uh.name key
in one, but it seems it can't see this.
May be this can be recorded as a needed feature for future releases?
When criticizing planner deficiencies, it's considered polite to use
something that's less than two major releases back ;-)
Sorry, it was blown out from my head at the end of composing: my version
is 8.0.1, not so old, IMHO.
CVS HEAD gets this right, although I need to go look at why it's
sticking a Materialize in there:
regression=# explain select * from (select * from tenk1 order by twothousand)
uh right join tenk1 log on log.thousand = uh.twothousand;
QUERY PLAN
---
Merge Right Join (cost=4575.77..6225.77 rows=10 width=488)
Merge Cond: (tenk1.twothousand = log.thousand)
-> Sort (cost=2287.89..2312.89 rows=1 width=244)
Sort Key: tenk1.twothousand
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244)
-> Materialize (cost=2287.89..2412.89 rows=1 width=244)
-> Sort (cost=2287.89..2312.89 rows=1 width=244)
Sort Key: log.thousand
-> Seq Scan on tenk1 log (cost=0.00..458.00 rows=1
width=244)
(9 rows)
[ pokes at it a bit more... ] 8.1 and 8.2 get it right for a plain
join, but not for an outer join. Strange, I need to check that too.
regards, tom lane
Best wishes,
Aleksandr.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Incomprehensible dogged sort in Merge Join
Alvaro Herrera wrote: Aleksandr Vinokurov escribió: Tom Lane wrote: When criticizing planner deficiencies, it's considered polite to use something that's less than two major releases back ;-) Sorry, it was blown out from my head at the end of composing: my version is 8.0.1, not so old, IMHO. It _is_ quite old, yes. Try 8.2 at the very least, but in some cases you're going to get "current CVS HEAD does it better", so if you want to suggest improvements to the planner you should be really looking into that. I understood my fault :) Guys, please forgive my dodge. Aleksandr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Incomprehensible dogged sort in Merge Join
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> >> What version are you running with? > > 8.0.1, sorry for missing. There have been 12 bug-fix releases since then on the 8.0 branch including updating timezones to reflect the new daylight saving time rules for North America, various crashes, data corruption, and security bugs. There is no reason not to upgrade to the current latest 8.0 branch release which is 8.0.13. Separately, you may not want to upgrade to 8.2 now for operational reasons but reporting "wishlist" bugs against a version that's two major releases old is almost certainly going to be pointless. Postgres is moving ahead so fast that the chances that your wish is granted already in more recent releases are very high. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Incomprehensible dogged sort in Merge Join
Aleksandr Vinokurov escribió: > Tom Lane wrote: >> When criticizing planner deficiencies, it's considered polite to use >> something that's less than two major releases back ;-) > > Sorry, it was blown out from my head at the end of composing: my version is > 8.0.1, not so old, IMHO. It _is_ quite old, yes. Try 8.2 at the very least, but in some cases you're going to get "current CVS HEAD does it better", so if you want to suggest improvements to the planner you should be really looking into that. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "No reniegues de lo que alguna vez creíste" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Reporting functions (OLAP)
[EMAIL PROTECTED] (Sean Davis) writes:
> I am looking for reporting extensions such as windowing, ranking,
> leads/lags, etc. for postgresql. A quick google search turned up
> some "working on it" type results, but I was wondering if anything
> actually existed up to this point?
No, nothing exists to this point.
Efforts are presently ongoing to get version 8.3 out the door, and,
for now, that is The High Priority.
Once 8.3 is released, people who have been working on 8.3 matters can
shift attention to new functionality.
SQL 2003 "windowing" is on the TODO list:
http://www.postgresql.org/docs/faqs.TODO.html
Some discussions of this...
http://svr5.postgresql.org/pgsql-hackers/2004-11/msg01093.php
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://linuxdatabases.info/info/linuxdistributions.html
"Every sufficientlyunreadable programming language contains a
reimplementation of APL and/or INTERCAL."-- Greenspun's Eleventh
Rule of Programming
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Incomprehensible dogged sort in Merge Join
[EMAIL PROTECTED] (Gregory Stark) writes: > "Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes: > >> Gregory Stark wrote: >>> >>> What version are you running with? >> >> 8.0.1, sorry for missing. > > There have been 12 bug-fix releases since then on the 8.0 branch including > updating timezones to reflect the new daylight saving time rules for North > America, various crashes, data corruption, and security bugs. There is no > reason not to upgrade to the current latest 8.0 branch release which is > 8.0.13. > > Separately, you may not want to upgrade to 8.2 now for operational > reasons but reporting "wishlist" bugs against a version that's two > major releases old is almost certainly going to be > pointless. Postgres is moving ahead so fast that the chances that > your wish is granted already in more recent releases are very high. Further, the chances of your wishes being granted on the 8.0 branch are somewhere between zero and none, and the changes since 8.0 are major enough that comparing against 8.0 for the purposes of discussion just generally isn't useful. -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://linuxfinances.info/info/oses.html Rule of Scarcity "If it's not allocated by a market, then it's more expensive than money." -- Mark Miller ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] How to influence the planner
I have a table, t1, with about 12 million rows The column foo is unique and indexed The column bar is not unique and is indexed "select * from t1 where length(bar) = 0;" runs about 2 minutes "select * from t1 where length(bar) = 0 order by foo ;" ran until I stopped it after about 20 minutes My simple solution is "select * into t2 from t1 where length(bar) = 0;" and "select * from t2 order by foo ;" Is there a way to make "select * from t1 where length(bar) = 0 order by foo ;" or something similar work Thanks Richard Ray ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to influence the planner
On Aug 31, 2007, at 13:32 , Richard Ray wrote: "select * from t1 where length(bar) = 0;" runs about 2 minutes "select * from t1 where length(bar) = 0 order by foo ;" ran until I stopped it after about 20 minutes EXPLAIN ANALYZE will help you see what the planner is doing to produce the results. Have you recently ANALYZEd t1? If length(bar) = 0 is a common operation on this table, you might consider using an expression index on t1: create index t1_length_bar_idx on t1 (length(bar)); You might want to ask on the performance list as well, as this is right up their alley. Hope this gets you started on the right track. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to influence the planner
On Fri, 31 Aug 2007, Michael Glaesemann wrote: On Aug 31, 2007, at 13:32 , Richard Ray wrote: "select * from t1 where length(bar) = 0;" runs about 2 minutes "select * from t1 where length(bar) = 0 order by foo ;" ran until I stopped it after about 20 minutes EXPLAIN ANALYZE will help you see what the planner is doing to produce the results. mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0 order by foo; QUERY PLAN --- Index Scan using t1_pkey on t1 (cost=0.00..46698478.18 rows=60038 width=334) (actual time=4612.740..2349582.520 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 2349614.258 ms (3 rows) mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0; QUERY PLAN --- Seq Scan on t1 (cost=1.00..102020349.17 rows=60038 width=334) (actual time=39.065..108645.233 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 108677.759 ms (3 rows) mda=# The index for foo on t1 is the primary index t1_pkey Why is it slower using the index Have you recently ANALYZEd t1? I run vacuum analyze nightly If length(bar) = 0 is a common operation on this table, you might consider using an expression index on t1: create index t1_length_bar_idx on t1 (length(bar)); This is a one time procedure to fix some data but I've had this problem before I'm running PostgreSQL 8.1.0 on Fedora Core 6 You might want to ask on the performance list as well, as this is right up their alley. Hope this gets you started on the right track. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to influence the planner
On Aug 31, 2007, at 16:07 , Richard Ray wrote: Total runtime: 2349614.258 ms (3 rows) Wow. Nearly 40 minutes! What are your work_mem set at? You may want to increase work_mem, as it might help with the sort. The index for foo on t1 is the primary index t1_pkey Why is it slower using the index Using an index requires first scanning the index and then looking up the value in the table, so depending on the number of rows that need to be returned, using an index might have more overhead than just reading every row of the table (i.e., a sequential scan). Have you recently ANALYZEd t1? I run vacuum analyze nightly That might not be often enough. Looking at the number of rows in the cost estimate (60K rows) and the actual number of rows (~30K rows), it looks like there's a factor of two difference. If length(bar) = 0 is a common operation on this table, you might consider using an expression index on t1: create index t1_length_bar_idx on t1 (length(bar)); This is a one time procedure to fix some data but I've had this problem before Depending on the time it takes to build the index, it might prove worthwhile even for a one-off query. You're pretty much doing this by using a temporary table though. I'm running PostgreSQL 8.1.0 on Fedora Core 6 You should upgrade 8.1.9, the latest in the 8.1.x series. This may not help your performance issues, but there have been 9 point releases since the version you're running which include bug and security fixes. Even better, upgrade to 8.2.4, as there may very well be performance improvements in 8.2 which help you. You could look through the 8.2 release notes to see if any might apply. Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to influence the planner
Richard Ray <[EMAIL PROTECTED]> writes: > On Fri, 31 Aug 2007, Michael Glaesemann wrote: >> EXPLAIN ANALYZE will help you see what the planner is doing to produce the >> results. > mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0 order by foo; > QUERY PLAN > --- > Index Scan using t1_pkey on t1 (cost=0.00..46698478.18 > rows=60038 width=334) (actual time=4612.740..2349582.520 rows=32705 > loops=1) > Filter: (length(bar) = 0) > Total runtime: 2349614.258 ms > (3 rows) > mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0; > QUERY PLAN > --- > Seq Scan on t1 (cost=1.00..102020349.17 rows=60038 > width=334) (actual time=39.065..108645.233 rows=32705 loops=1) > Filter: (length(bar) = 0) > Total runtime: 108677.759 ms > (3 rows) The problem here is you've got enable_seqscan = off. Don't do that. That will make it use an index if it possibly can, whether using one is a good idea or not. In this case, since the useful condition on length(bar) is not indexable, the best available index-using scan uses the index to implement order by foo ... which is pointless here in terms of saving runtime. > I'm running PostgreSQL 8.1.0 on Fedora Core 6 Please update. There are a *lot* of bugs fixed in the 8.1.x series since then. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to influence the planner
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Aug 31, 2007, at 16:07 , Richard Ray wrote: >>> If length(bar) = 0 is a common operation on this table, you might >>> consider using an expression index on t1: >> >>> create index t1_length_bar_idx on t1 (length(bar)); >> >> This is a one time procedure to fix some data but I've had this >> problem before Actually, I just noticed that the OP does have an index on bar, which means (assuming it's a string data type) that this query is equivalent to select * from t1 where bar = '' which would be a far preferable way to do it because that condition can use the index. The Postgres planner is fairly data-type-agnostic and does not have the knowledge that these are equivalent queries, so you can't expect it to make that substitution for you. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to influence the planner
On Fri, 31 Aug 2007, Tom Lane wrote: Richard Ray <[EMAIL PROTECTED]> writes: On Fri, 31 Aug 2007, Michael Glaesemann wrote: EXPLAIN ANALYZE will help you see what the planner is doing to produce the results. mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0 order by foo; QUERY PLAN --- Index Scan using t1_pkey on t1 (cost=0.00..46698478.18 rows=60038 width=334) (actual time=4612.740..2349582.520 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 2349614.258 ms (3 rows) mda=# EXPLAIN ANALYZE select * from t1 where length(bar) = 0; QUERY PLAN --- Seq Scan on t1 (cost=1.00..102020349.17 rows=60038 width=334) (actual time=39.065..108645.233 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 108677.759 ms (3 rows) The problem here is you've got enable_seqscan = off. Don't do that. That will make it use an index if it possibly can, whether using one is a good idea or not. In this case, since the useful condition on length(bar) is not indexable, the best available index-using scan uses the index to implement order by foo ... which is pointless here in terms of saving runtime. I'm running PostgreSQL 8.1.0 on Fedora Core 6 Please update. There are a *lot* of bugs fixed in the 8.1.x series since then. Changing to enable_seqscan = on does solve this problem, thanks Is there some method of crafting a query that will assert my wishes to the planner mda=# EXPLAIN ANALYZE select * from (select * from t1 where length(bar) = 0) a order by foo; QUERY PLAN Index Scan using t1_pkey on t1 (cost=0.00..46698482.18 rows=60038 width=334) (actual time=4784.869..2317363.298 rows=32705 loops=1) Filter: (length(bar) = 0) Total runtime: 2317395.137 ms (3 rows) When is enable_seqscan = off appropriate regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
