Re: suggestions on improving a query

On Wed, 2007-02-14 at 10:55 -0500, Tom Lane wrote:
> Rajarshi Guha <rguha(at)indiana(dot)edu> writes:
> > Clearly a big improvement in performance.
>
> Huh? It looks like exactly the same plan as before. Any improvement
> you're seeing must be coming from cache effects.

Well the new run was done nearly 8 hours after the initial one - I
would've thought that the cache had been purged (?)

> > It looks like theres a big mismatch on the expected and observed costs and times.
>
> In fact I'd say the whole problem here
> is that the planner is being too optimistic about the benefits of a
> fast-start plan. For whatever reason (most likely, an unfavorable
> correlation between dock.target and dockscore_plp.total), the desired
> rows aren't uniformly scattered in the output of the join, and so it's
> taking longer than expected to find 10 of them.

Is there any way to solve this? I've increased the statistics target on
dockscore_plp.total to 100 - does going higher help?

>From what you've said, it appears that the problem is arising due to
lack of correlation between two columns in two tables.

This is strange since, out of 4.6M rows in dock, ~ 960K will be selected
and the corresponding 960K rows from dockscore_plp will be ordered and
then the top 10 will be taken.

So does the lack of correlation occur due to 'ordering' in the DB
itself? And if this is the case, how does one fix the lack of
correlation (if at all possible)?

-------------------------------------------------------------------
Rajarshi Guha <rguha(at)indiana(dot)edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
Regular naps prevent old age....
especially if you take them while driving