Skip site navigation (1) Skip section navigation (2)

Re: Searching for the cause of a bad plan

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Searching for the cause of a bad plan
Date: 2007-09-24 14:04:42
Message-ID: 1190642682.4661.247.camel@PCD12478 (view raw or flat)
Thread:
Lists: pgsql-performance
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)

This is if the "b" values in table_a wouldn't be correlated at all with
the content of table_b2. The reality is that they are, but the planner
has no information about that.

I have no idea how the planner works though, so this might be totally
off...

I will copy the data and send the results (not promising though that it
will be today).

Cheers,
Csaba.



In response to

Responses

pgsql-performance by date

Next:From: Gábor FarkasDate: 2007-09-24 14:07:16
Subject: select count(*) performance (vacuum did not help)
Previous:From: brauagustin-suscDate: 2007-09-24 13:59:26
Subject: Re: Low CPU Usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group