That fixed it :)
The 9.2 query plan for reference:
Sort (cost=439.67..439.74 rows=30 width=503) (actual time=0.754..0.756 rows=49 loops=1)
Sort Key: s0_m0_msubjobs.creation_mdate, s0_m1_mjob.__id, s0_m0_msubjobs.__id
Sort Method: quicksort Memory: 31kB
-> Hash Join (cost=23.45..438.93 rows=30 width=503) (actual time=0.213..0.718 rows=49 loops=1)
Hash Cond: (s0_m0_msubjobs.job_mid = s0_m1_mjob.__id)
-> Nested Loop Semi Join (cost=5.20..420.27 rows=30 width=484) (actual time=0.054..0.543 rows=49 loops=1)
-> Bitmap Heap Scan on "subJobs" s0_m0_msubjobs (cost=5.20..54.08 rows=30 width=484) (actual time=0.040..0.102 rows=49 loops=1)
Recheck Cond: ((status)::text = 'IN_PROGRESS'::text)
Filter: ((validation_mrequired)::text = 'Y'::text)
Rows Removed by Filter: 76
-> Bitmap Index Scan on subjob_status (cost=0.00..5.19 rows=125 width=0) (actual time=0.029..0.029 rows=125 loops=1)
Index Cond: ((status)::text = 'IN_PROGRESS'::text)
-> Nested Loop (cost=0.00..307.45 rows=307 width=16) (actual time=0.009..0.009 rows=1 loops=49)
-> Index Only Scan using "subJobs_mid_mindex" on "subJobs" s1_m0_msubjobs (cost=0.00..5.34 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=49)
Index Cond: (__id = s0_m0_msubjobs.__id)
Heap Fetches: 49
-> Index Scan using "unit_msubJobs_mid_mindex" on unit s1_m1_munit (cost=0.00..299.03 rows=307 width=8) (actual time=0.006..0.006 rows=1 loops=49)
Index Cond: ("subJobs_mid" = s1_m0_msubjobs.__id)
Filter: ((validated IS NULL) OR ((validated)::text = 'N'::text))
-> Hash (cost=12.00..12.00 rows=500 width=27) (actual time=0.149..0.149 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 30kB
-> Seq Scan on job s0_m1_mjob (cost=0.00..12.00 rows=500 width=27) (actual time=0.003..0.071 rows=500 loops=1)
Total runtime: 0.818 ms
Great work,
Nick Hofstede
-----Original Message-----
From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx]
Sent: woensdag 3 oktober 2012 5:55
To: Nick Hofstede
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: suboptimal query plan
Nick Hofstede <Nick.Hofstede@xxxxxxxxxxxxxxxxxx> writes:
> I'm struggling with a query that seems to use a suboptimal query plan.
Try it in 9.2 - this is the same type of join ordering restriction complained of last week here:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00201.php
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
________________________________
Inventive Designers' Email Disclaimer:
http://www.inventivedesigners.com/email-disclaimer
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
