Retry: Is this possible / slow performance? - Mailing list pgsql-performance

From Joost Kraaijeveld
Subject Retry: Is this possible / slow performance?
Date
Msg-id A3D1526C98B7C1409A687E0943EAC410605EF2@obelix.askesis.nl
Whole thread Raw
Responses Re: Retry: Is this possible / slow performance?
List pgsql-performance
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.



pgsql-performance by date:

Previous
From: Markus Schaber
Date:
Subject: Re: Can the V7.3 EXPLAIN ANALYZE be trusted?
Next
From: Tom Lane
Date:
Subject: Re: Retry: Is this possible / slow performance?