pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad plan when join on function
7+ messages / 5 participants
[nested] [flat]
* Bad plan when join on function
@ 2011-01-17 08:03 Zotov <zotov@oe-it.ru>
0 siblings, 2 replies; 7+ messages in thread
From: Zotov @ 2011-01-17 08:03 UTC (permalink / raw)
To: pgsql-performance
It`s just a sample.
select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
"Nested Loop (cost=0.00..786642.96 rows=1 width=4) (actual
time=91021.167..119601.344 rows=1 loops=1)"
" Join Filter: ((a.id)::integer = asinteger((c.id)::integer))"
" -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
time=0.007..0.008 rows=1 loops=1)"
" -> Seq Scan on abstract a (cost=0.00..442339.78 rows=22953478
width=4) (actual time=0.003..115193.283 rows=22953478 loops=1)"
"Total runtime: 119601.428 ms"
select c.id from OneRow c join abstract a on a.id=c.id
"Nested Loop (cost=0.00..13.85 rows=1 width=4) (actual
time=254.579..254.585 rows=1 loops=1)"
" -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
time=0.006..0.007 rows=1 loops=1)"
" -> Index Scan using integ_1197 on abstract a (cost=0.00..12.83
rows=1 width=4) (actual time=254.559..254.563 rows=1 loops=1)"
" Index Cond: ((a.id)::integer = (c.id)::integer)"
"Total runtime: 254.648 ms"
OneRow Contains only one row,
abstract contains 22 953 500 rows
AsInteger is simple function on Delphi
it just return input value
CREATE OR REPLACE FUNCTION asinteger(integer)
RETURNS integer AS
'oeudfpg.dll', 'AsInteger'
LANGUAGE c VOLATILE
COST 1;
Why SeqScan???
this query is simple sample to show SLOW seq scan plan
I have a real query what i don`t know when it will be done... but at
firebird this query with full fetch 1-2 minutes
I can`t give you this real query and database (database size is more,
than 20 GB)
as i see that query have same problem as this sample
It`s so sad, because I spend so much time to support posgtresql in my
project and now i see what more queries is slower more than 10 times...
Please HELP!
PostgreSQL version 9.0.2
--
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov@oe-it.ru
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-17 20:12 Pavel Stehule <pavel.stehule@gmail.com>
parent: Zotov <zotov@oe-it.ru>
1 sibling, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2011-01-17 20:12 UTC (permalink / raw)
To: Zotov <zotov@oe-it.ru>; +Cc: pgsql-performance
2011/1/17 Zotov <zotov@oe-it.ru>:
> It`s just a sample.
>
> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> "Nested Loop (cost=0.00..786642.96 rows=1 width=4) (actual
> time=91021.167..119601.344 rows=1 loops=1)"
> " Join Filter: ((a.id)::integer = asinteger((c.id)::integer))"
> " -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.007..0.008 rows=1 loops=1)"
> " -> Seq Scan on abstract a (cost=0.00..442339.78 rows=22953478 width=4)
> (actual time=0.003..115193.283 rows=22953478 loops=1)"
> "Total runtime: 119601.428 ms"
>
>
> select c.id from OneRow c join abstract a on a.id=c.id
>
> "Nested Loop (cost=0.00..13.85 rows=1 width=4) (actual
> time=254.579..254.585 rows=1 loops=1)"
> " -> Seq Scan on onerow c (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.006..0.007 rows=1 loops=1)"
> " -> Index Scan using integ_1197 on abstract a (cost=0.00..12.83 rows=1
> width=4) (actual time=254.559..254.563 rows=1 loops=1)"
> " Index Cond: ((a.id)::integer = (c.id)::integer)"
> "Total runtime: 254.648 ms"
>
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value
>
> CREATE OR REPLACE FUNCTION asinteger(integer)
> RETURNS integer AS
> 'oeudfpg.dll', 'AsInteger'
> LANGUAGE c VOLATILE
> COST 1;
are you sure so your function needs a VOLATILE flag?
Regards
Pavel Stehule
>
>
> Why SeqScan???
>
> this query is simple sample to show SLOW seq scan plan
> I have a real query what i don`t know when it will be done... but at
> firebird this query with full fetch 1-2 minutes
> I can`t give you this real query and database (database size is more, than
> 20 GB)
> as i see that query have same problem as this sample
> It`s so sad, because I spend so much time to support posgtresql in my
> project and now i see what more queries is slower more than 10 times...
> Please HELP!
>
> PostgreSQL version 9.0.2
>
> --
> С уважением,
> Зотов Роман Владимирович
> руководитель Отдела инструментария
> ЗАО "НПО Консультант"
> г.Иваново, ул. Палехская, д. 10
> тел./факс: (4932) 41-01-21
> mailto: zotov@oe-it.ru
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-17 20:51 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2011-01-17 20:51 UTC (permalink / raw)
To: zotov@oe-it.ru; pgsql-performance
Zotov wrote:
> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
> Why SeqScan???
Because you don't have an index on AsInteger(c.id).
If your function is IMMUTABLE (each possible combination of input
values always yields the same result), and you declare it such, then
you can index on the function, and it will perform at a speed similar
to the other example.
-Kevin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-17 21:24 Pavel Stehule <pavel.stehule@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2011-01-17 21:24 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: zotov@oe-it.ru; pgsql-performance
2011/1/17 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Zotov wrote:
>
>> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
>> Why SeqScan???
>
> Because you don't have an index on AsInteger(c.id).
>
> If your function is IMMUTABLE (each possible combination of input
> values always yields the same result), and you declare it such, then
> you can index on the function, and it will perform at a speed similar
> to the other example.
it should to work without functional index - but not sure about effectivity
postgres=# explain select 1 from a join b on a.f = sin(b.f);
QUERY PLAN
-----------------------------------------------------------------------------
Merge Join (cost=809.39..1352.64 rows=10000 width=0)
Merge Cond: (a.f = (sin(b.f)))
-> Index Scan using a_f_idx on a (cost=0.00..318.25 rows=10000 width=8)
-> Sort (cost=809.39..834.39 rows=10000 width=8)
Sort Key: (sin(b.f))
-> Seq Scan on b (cost=0.00..145.00 rows=10000 width=8)
(6 rows)
but functional index always helps
postgres=# create index on b((sin(f)));
CREATE INDEX
postgres=# explain select 1 from a join b on a.f = sin(b.f);
QUERY PLAN
-------------------------------------------------------------------------------
Merge Join (cost=0.00..968.50 rows=10000 width=0)
Merge Cond: (a.f = sin(b.f))
-> Index Scan using a_f_idx on a (cost=0.00..318.25 rows=10000 width=8)
-> Index Scan using b_sin_idx on b (cost=0.00..450.25 rows=10000 width=8)
(4 rows)
regards
Pavel Stehule
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-17 21:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2011-01-17 21:33 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; zotov@oe-it.ru; pgsql-performance
Pavel Stehule <pavel.stehule@gmail.com> writes:
> it should to work without functional index - but not sure about effectivity
As long as the function is VOLATILE, the planner can't use any
intelligent query plan. Merge or hash join both require at least
stable join keys.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-17 21:37 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2011-01-17 21:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; zotov@oe-it.ru; pgsql-performance
2011/1/17 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> it should to work without functional index - but not sure about effectivity
>
> As long as the function is VOLATILE, the planner can't use any
> intelligent query plan. Merge or hash join both require at least
> stable join keys.
sure, my first advice was a question about function volatility - and
my sentence was related to using immutable function.
regards
Pavel Stehule
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Bad plan when join on function
@ 2011-01-18 14:15 Shaun Thomas <sthomas@peak6.com>
parent: Zotov <zotov@oe-it.ru>
1 sibling, 0 replies; 7+ messages in thread
From: Shaun Thomas @ 2011-01-18 14:15 UTC (permalink / raw)
To: Zotov <zotov@oe-it.ru>; +Cc: pgsql-performance
On 01/17/2011 02:03 AM, Zotov wrote:
> select c.id from OneRow c join abstract a on a.id=AsInteger(c.id)
>
> OneRow Contains only one row,
> abstract contains 22 953 500 rows
>
> AsInteger is simple function on Delphi
> it just return input value
Ok... there has to be some kind of misunderstanding, here. First of all,
if you're trying to cast a value to an integer, there are so many
built-in ways to do this, I can't imagine why you'd call a C function.
The most common for your example would be ::INT.
Second, you need to understand how the optimizer works. It doesn't know
what the function will return, so it has to apply the function to every
row in your 'abstract' table. You can get around this by applying an
index to your table with the result of your function, but to do that,
you'll have to mark your function as STABLE or IMMUTABLE instead of
VOLATILE.
Joining on the result of a function will always do this. The database
can't know what your function will return. If you can avoid using a
function in your join clause, do so.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-01-18 14:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-17 08:03 Bad plan when join on function Zotov <zotov@oe-it.ru>
2011-01-17 20:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-01-18 14:15 ` Shaun Thomas <sthomas@peak6.com>
2011-01-17 20:51 Re: Bad plan when join on function Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-01-17 21:24 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-01-17 21:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-17 21:37 ` Pavel Stehule <pavel.stehule@gmail.com>
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