Re: Redundant sub query triggers slow nested loop left join

From: henk de wit
Subject: Re: Redundant sub query triggers slow nested loop left join
Date: ,
Msg-id: BAY106-F24FD9F24DB0912FF7D4AD2F5540@phx.gbl
(view: Whole thread, Raw)
In response to: Re: Redundant sub query triggers slow nested loop left join  (Tom Lane)
Responses: Re: Redundant sub query triggers slow nested loop left join  (Tom Lane)
List: pgsql-performance


>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/



pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: postgres: 100% CPU utilization
From: "Merlin Moncure"
Date:
Subject: Re: seeking advise on char vs text or varchar in search table