From: Simon Riggs <simon@2ndquadrant.com>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Mon, 24 Sep 2007 18:55:22 +0100
Message-ID: <1190656522.4181.228.camel@ebony.site> (raw)
In-Reply-To: <1190642682.4661.247.camel@PCD12478>
References: <1190369024.4661.173.camel@PCD12478>
<6175.1190390913@sss.pgh.pa.us>
<1190396352.4202.109.camel@ebony.site>
<13644.1190397220@sss.pgh.pa.us>
<1190399879.4202.127.camel@ebony.site>
<27857.1190417434@sss.pgh.pa.us>
<1190640429.4181.165.camel@ebony.site>
<1190642682.4661.247.camel@PCD12478>
On Mon, 2007-09-24 at 16:04 +0200, Csaba Nagy wrote:
> On Mon, 2007-09-24 at 14:27 +0100, Simon Riggs wrote:
> > Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> > then re-post the EXPLAINs, with stats data.
>
> Well, I can of course. I actually tried to generate some random data
> with similar record count and relations between the tables (which I'm
> not sure I succeeded at), without the extra columns, but it was happily
> yielding the nested loop plan. So I guess I really have to copy the
> whole data (several tens of GB).
>
> But from my very limited understanding of what information is available
> for the planner, I thought that the record count estimated for the join
> between table_a and table_b1 on column b should be something like
>
> (estimated record count in table_a for value "a") * (weight of "b" range
> covered by table_b1 and table_a in common) / (weight of "b" range
> covered by table_a)
There's no such code I'm aware of. Sounds a good idea though. I'm sure
we could do something with the histogram values, but we don't in the
default selectivity functions.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
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: simon@2ndquadrant.com, nagy@ecircle-ag.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190656522.4181.228.camel@ebony.site>
* 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