Re: Simple Join - Mailing list pgsql-performance

From Mark Kirkwood
Subject Re: Simple Join
Date
Msg-id 43A1C40D.8040006@paradise.net.nz
Whole thread Raw
In response to Re: Simple Join  (Mitch Skinner <lists@arctur.us>)
Responses Overriding the optimizer  ("Craig A. James" <cjames@modgraph-usa.com>)
Re: Simple Join  (David Lang <dlang@invendra.net>)
List pgsql-performance
Mitch Skinner wrote:
> I saw that; what I'm suggesting is that that you try creating a 3-column
> index on ordered_products using the paid, suspended_sub, and id columns.
> In that order, I think, although you could also try the reverse.  It may
> or may not help, but it's worth a shot--the fact that all of those
> columns are used together in the query suggests that you might do better
> with a three-column index on those.
>
> With all three columns indexed individually, you're apparently not
> getting the bitmap plan that Mark is hoping for.  I imagine this has to
> do with the lack of multi-column statistics in postgres, though you
> could also try raising the statistics target on the columns of interest.
>
> Setting enable_seqscan to off, as others have suggested, is also a
> worthwhile experiment, just to see what you get.
>
>

Right on. Some of these "coerced" plans may perform much better. If so,
we can look at tweaking your runtime config: e.g.

effective_cache_size
random_page_cost
default_statistics_target

to see if said plans can be chosen "naturally".

cheers

Mark

pgsql-performance by date:

Previous
From: "Gregory S. Williamson"
Date:
Subject: Re: [postgis-users] Is my query planner failing me, or vice versa?
Next
From: "Anjan Dave"
Date:
Subject: Re: SAN/NAS options