Re: suboptimal query plan - Mailing list pgsql-performance

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.102rows=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.029rows=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@sss.pgh.pa.us]
Sent: woensdag 3 oktober 2012 5:55
To: Nick Hofstede
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] suboptimal query plan

Nick Hofstede <Nick.Hofstede@inventivegroup.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@postgresql.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