Re: suboptimal query plan

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: woensdag 3 oktober 2012 5:55
To: Nick Hofstede
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] suboptimal query plan

Nick Hofstede <Nick(dot)Hofstede(at)inventivegroup(dot)com> 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(at)postgresql(dot)org)
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