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
