pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Віталій Тимчишин <tivv00@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very specialised query
Date: Wed, 1 Apr 2009 00:11:52 +0300
Message-ID: <331e40660903311411k532df0e3jeeffdb184adba9e6@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.0903311757030.21772@aragorn.flymine.org>
References: <alpine.DEB.2.00.0903261313290.21772@aragorn.flymine.org>
	<331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com>
	<alpine.DEB.2.00.0903271708080.21772@aragorn.flymine.org>
	<2298.1238179393@sss.pgh.pa.us>
	<alpine.DEB.2.00.0903301618190.21772@aragorn.flymine.org>
	<331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com>
	<alpine.DEB.2.00.0903311757030.21772@aragorn.flymine.org>

>
>
> The outer nested join has the VALUES as the main loop, and the complicated
> join as the leaf. So, the complicated overlap-finding join gets run twice.


That's weird. What do you have as statistics target? Planner is incorrect
few orders of magnitude, so increasing it may help.
BTW: One of constraints is redundant l1.start <= l2.start implies l1.start
<= l2.end, so latter can be removed as for me.


>
>
> Oh, there's also the great big sort and unique, but I think I can get rid
> of that.
>

As far as I can see, duplicates will occur if and only if l1.start ==
l2.start && l1.end == l2.end.
That can be easily filtered by adding "where n=1 or l1.start != l2.start or
l1.end != l2.end" to outer select.

view thread (29+ messages)  latest in thread

Message-ID: <331e40660903311411k532df0e3jeeffdb184adba9e6@mail.gmail.com>
Permalink:  ../331e40660903311411k532df0e3jeeffdb184adba9e6@mail.gmail.com/
Also on:    postgresql.org/message-id/331e40660903311411k532df0e3jeeffdb184adba9e6@mail.gmail.com

 · 

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: tivv00@gmail.com, matthew@flymine.org
  Subject: Re: Very specialised query
  In-Reply-To: <331e40660903311411k532df0e3jeeffdb184adba9e6@mail.gmail.com>

* 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