pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
(no subject)
3+ messages / 3 participants
[nested] [flat]

* (no subject)
@ 2003-12-24 16:25  Michael Rothschild <michael.r@corigin.co.il>
  0 siblings, 2 replies; 3+ messages in thread

From: Michael Rothschild @ 2003-12-24 16:25 UTC (permalink / raw)
  To: pgsql-performance

I consider using PostgreSQL for a project we have in our company and, to
get a better picture of the product, I started scanning its source code
and internal documentation.
Based on what I saw (and maybe I didn't see enough) it seems that the
optimizer will always decide to repeatedly scan the whole row set
returned by sub selects in the context of an IN clause sequentially, as
opposed to what I would expect it to do (which is to create some index
or hash structure to improve performance).
For example, if I have the following query:
Select * from a where x in (select y from b where z=7) 
Then I would expect an index or hash structure to be created for b.y
when it is first scanned and brought into the cache but I couldn't see
it happening in the source.
As I said, I only inferred it from reading the source - not from actual
experiments - so I may be wrong.
1. Am I wrong?
2. If I'm right, is there any plan to change it (after all, in the
context of an IN clause, an index on the returned row set is all that is
needed - the row set itself does not seem to matter).
 
Thank you,
 
Michael Rothschild

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re:
@ 2003-12-25 02:47  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Michael Rothschild <michael.r@corigin.co.il>
  1 sibling, 0 replies; 3+ messages in thread

From: Christopher Kings-Lynne @ 2003-12-25 02:47 UTC (permalink / raw)
  To: Michael Rothschild <michael.r@corigin.co.il>; +Cc: pgsql-performance

> For example, if I have the following query:
> Select * from a where x in (select y from b where z=7)
> Then I would expect an index or hash structure to be created for b.y
> when it is first scanned and brought into the cache but I couldn't see
> it happening in the source.
> As I said, I only inferred it from reading the source - not from actual
> experiments - so I may be wrong.
> 1. Am I wrong?

You are wrong - this is old behaviour and one of the major speed
improvements of PostgreSQL 7.4 is that IN subqueries now use a hash index
and hence they are much faster.

Chris





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re:
@ 2003-12-25 16:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michael Rothschild <michael.r@corigin.co.il>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2003-12-25 16:28 UTC (permalink / raw)
  To: Michael Rothschild <michael.r@corigin.co.il>; +Cc: pgsql-performance

"Michael Rothschild" <michael.r@corigin.co.il> writes:
> Based on what I saw (and maybe I didn't see enough) it seems that the
> optimizer will always decide to repeatedly scan the whole row set
> returned by sub selects in the context of an IN clause sequentially,

What version were you looking at?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-12-25 16:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-24 16:25  Michael Rothschild <michael.r@corigin.co.il>
2003-12-25 02:47 ` Re: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-12-25 16:28 ` Re: Tom Lane <tgl@sss.pgh.pa.us>

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