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
Message-ID: <BAY106-F24FD9F24DB0912FF7D4AD2F5540@phx.gbl> (raw)
In-Reply-To: <10143.1177274553@sss.pgh.pa.us>
>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/
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: henk53602@hotmail.com, tgl@sss.pgh.pa.us
Subject: Re: Redundant sub query triggers slow nested loop left join
In-Reply-To: <BAY106-F24FD9F24DB0912FF7D4AD2F5540@phx.gbl>
* 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