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