From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Zotov <zotov(at)oe-it(dot)ru> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Bad plan when join on function |
Date: | 2011-01-17 20:12:31 |
Message-ID: | AANLkTin9AJ1Y8pyO1vwrbB1Hjw+dteY0776ZVGnqK9T4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2011/1/17 Zotov <zotov(at)oe-it(dot)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(at)oe-it(dot)ru
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-01-17 20:24:26 | Re: Possible to improve query plan? |
Previous Message | Ing. Marcos Ortiz Valmaseda | 2011-01-17 16:52:27 | Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2 |