pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Denes Daniel <panther-d@freemail.hu>
To: pgsql-performance@postgresql.org
Subject: Re: Query planner unaware of possibly best plan
Date: Sat, 22 Sep 2007 02:08:43 +0200 (CEST)
Message-ID: <freemail.20070822020843.13431@fm03.freemail.hu> (raw)
In-Reply-To: <1190405239.4202.131.camel@ebony.site>

Simon Riggs <simon@2ndquadrant.com> wrote:

> On Fri, 2007-09-21 at 21:20 +0200, Dániel Dénes wrote:
> 
> > The costs may be different because I've tuned the query planner's 
> > parameters.
> 
> OK, understood.
> 
> > > Ordering by parent, child is fairly common but the variation you've
> > > got here isn't that common.
> > How else can you order by parent, child other than first ordering by
> > a unique key of parent, then something in child? (Except for 
> > child.parent_id, child.something because this has all the
> > information in child and can rely on a single multicolumn index.)
> 
> Why "except"? Whats wrong with ordering that way? 
> 
> Make the case. **I** want it is not sufficient...
> 
> -- 
>   Simon Riggs
>   2ndQuadrant  http://www.2ndQuadrant.com




In reply to Simon Riggs <simon@2ndquadrant.com>:

> > How else can you order by parent, child other than first ordering by
> > a unique key of parent, then something in child? (Except for 
> > child.parent_id, child.something because this has all the
> > information in child and can rely on a single multicolumn index.)
> 
> Why "except"? Whats wrong with ordering that way?

Well, nothing, but what if I have to order by some other unique key? Of 
course I could do that by redundantly storing the parent's data in child 
and then creating a multicolumn index, but...

Just to see clear: when I found this, I was trying to make a slightly 
different query. It was like:

SELECT *
FROM tparent JOIN tchild ON tchild.par_id = tparent.id
WHERE tparent.uniqcol1 = 123
ORDER BY tparent.uniqcol2, tchild.ord;

where there was a unique index on (tparent.uniqcol1, tparent.uniqcol2) 
and the columns are marked NOT NULL.
I expected a plan like doing an index scan on parent.uniqcol2 where 
uniqcol1 = 123, and (using a nestloop and child's pkey) joining in the 
children in the correct order (without a sort). But I got something else, 
so I tried everything to get what I wanted -- just to see the costs why 
the planner chose something else. After some time I found out that 
there is no such plan, so no matter what I do it will sort...
So that's how I got here. But since the original problem isn't that clean 
& simple, I thought I'd make a test case, that's easy to follow, and 
illustrates the problem: that the planner doesn't even consider my 
plan. If it did, I think that'd be the one that gets executed. But tell me if 
I'm wrong somewhere.



> Make the case. **I** want it is not sufficient...

Sorry, I can't understand that... I'm far from perfect in english. Please 
clarify so I can do what you ask me to.


Denes Daniel
-----------------------------------------------------




Olvasd az [origo]-t a mobilodon: mini magazinok a Mobizin-en
___________________________________________________
www.t-mobile.hu/mobizin




view thread (8+ messages)  latest in thread

Message-ID: <freemail.20070822020843.13431@fm03.freemail.hu>
Permalink:  ../freemail.20070822020843.13431@fm03.freemail.hu/
Also on:    postgresql.org/message-id/freemail.20070822020843.13431@fm03.freemail.hu

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: panther-d@freemail.hu
  Subject: Re: Query planner unaware of possibly best plan
  In-Reply-To: <freemail.20070822020843.13431@fm03.freemail.hu>
  References: <1190405239.4202.131.camel@ebony.site> <freemail.20070822020843.13431@fm03.freemail.hu>

* 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