Hi all,
A retry of the question asked before. All tables freshly vacuumed an analized.
Two queries: one with "set enable_seqscan = on" , the other with "set enable_seqscan = off". The first query lasts
59403ms, the second query 31 ms ( the desc order variant has the same large difference: 122494 ms vs. 1297 ms). (for
thequery plans see below).
Can I, without changing the SQL (because it is generated by a tool) or explicitely setting "set enable_seqscan = off"
forthis query, trick PostgreSQL in taking the fast variant of the queryplan?
TIA
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
------------------------------- Query 1
begin;
set enable_seqscan = on;
declare SQL_CUR01 cursor for
SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer
ORDER BY A.klantnummer;
fetch 100 in SQL_CUR01;
commit;
QUERY PLAN
Sort (cost=259968.77..262729.72 rows=1104380 width=12)
Sort Key: a.klantnummer, a.ordernummer
-> Hash Left Join (cost=42818.43..126847.70 rows=1104380 width=12)
Hash Cond: ("outer".klantnummer = "inner".klantnummer)
-> Seq Scan on orders a (cost=0.00..46530.79 rows=1104379 width=8)
-> Hash (cost=40635.14..40635.14 rows=368914 width=4)
-> Seq Scan on klt_alg b (cost=0.00..40635.14 rows=368914 width=4)
Actual running time: 59403 ms.
------------------------------- Query 2
begin;
set enable_seqscan = off;
declare SQL_CUR01 cursor for
SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer
ORDER BY A.klantnummer;
fetch 100 in SQL_CUR01;
commit;
QUERY PLAN
Merge Left Join (cost=0.00..2586604.86 rows=1104380 width=12)
Merge Cond: ("outer".klantnummer = "inner".klantnummer)
-> Index Scan using orders_klantnummer on orders a (cost=0.00..2435790.17 rows=1104379 width=8)
-> Index Scan using klt_alg_klantnummer on klt_alg b (cost=0.00..44909.11 rows=368914 width=4)
Actual running time: 31 ms.
