Message-ID: <BAY106-F24FD9F24DB0912FF7D4AD2F5540@phx.gbl>
In-Reply-To: <10143.1177274553@sss.pgh.pa.us>
From: "henk de wit" <henk53602@hotmail.com>
To: tgl@sss.pgh.pa.us
Cc: pgsql-performance@postgresql.org
Subject: Re: Redundant sub query triggers slow nested loop left join
Date: Mon, 23 Apr 2007 00:39:04 +0200
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

>You might be able to improve matters by increasing the statistics target
>for this table.

I have tried to increase the statistics for the status column to the maximum 
of 1000. After that I performed an analyze, vacuum analyze and vacuum full 
analyze on the table. Unfortunately this didn't seem to make any difference.

>I have a bad feeling though that the problem may be
>lack of cross-column statistics

I assume this isn't a thing that can be tweaked/increased in PG 8.2?

>--- the thing is evidently assuming
>that only about 1 in 200 rows have status = '0', which might be accurate
>as a global average but not for this particular merchant.  What exactly
>is the relationship between status and merchant_id, anyway?

The meaning is that a "banners_link" belongs to a merchant with the id 
merchant_id. A "banners_link" can be disabled (status 1) or enabled (status 
0). Globally about 1/3 of the banners_links have status 0 and 2/3 have 
status 1. The 1 in 200 estimate is indeed way off.

For the merchant in question the numbers are a bit different though, but not 
that much. Out of 424 rows total, 359 have status 0 and 65 have status 1.

_________________________________________________________________
FREE pop-up blocking with the new Windows Live Toolbar - get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/