pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Frank van Vugt <ftm.van.vugt@foxi.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??
Date: Tue, 15 Jun 2004 09:23:19 -0400
Message-ID: <296.1087305799@sss.pgh.pa.us> (raw)
In-Reply-To: <200406150849.29470.ftm.van.vugt@foxi.nl>
References: <200406150849.29470.ftm.van.vugt@foxi.nl>

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> If I ditch the group by, then this contraint does get pushed into the 
> subselect :

You're misinterpreting it.  Without the group by, the plan is a
candidate for nestloop-with-inner-index-scan; with the group by,
there's another step in the way.

Pushing down into subselects does get done, for instance in CVS tip
I can change the last part of your query to "foo.article_id < 50"
and get

 Hash Join  (cost=25.18..53.53 rows=335 width=8)
   Hash Cond: ("outer".id = "inner".article_id)
   ->  Seq Scan on article  (cost=0.00..20.00 rows=1000 width=4)
   ->  Hash  (cost=25.01..25.01 rows=67 width=8)
         ->  Subquery Scan foo  (cost=24.17..25.01 rows=67 width=8)
               ->  HashAggregate  (cost=24.17..24.34 rows=67 width=8)
                     ->  Seq Scan on package  (cost=0.00..22.50 rows=334 width=8)
                           Filter: (article_id < 50)

Obviously this is on toy tables, but the point is that the constraint
does get pushed down through the GROUP BY when appropriate.

			regards, tom lane




view thread (5+ messages)  latest in thread

Message-ID: <296.1087305799@sss.pgh.pa.us>
Permalink:  ../296.1087305799@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/296.1087305799@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ftm.van.vugt@foxi.nl
  Subject: Re: why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??
  In-Reply-To: <296.1087305799@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox