Skip site navigation (1) Skip section navigation (2)

Re: Query only slow on first run

From: cluster <skrald(at)amossen(dot)dk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query only slow on first run
Date: 2007-11-27 22:51:40
Message-ID: fii71p$oc1$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
> As for optimizing the query, I noticed that all three joins are done by
> nested loops.  I wonder if another join method would be faster.  Have you
> analyzed all the tables?

Yes. I did a VACUUM FULL ANALYZE before running the test queries. Also I 
have just performed an ANALYZE just to be sure everything was really 
analyzed.

> You aren't disabling hash joins or merge joins are
> you?

Nope.

>  If you aren't, then as a test I would try disabling nested loops by
> doing "set enable_nestloop=false" and see if the query is any faster for
> you.

If I disable the nested loops, the query becomes *much* slower.

A thing that strikes me is the following. As you can see I have the 
constraint: q.status = 1. Only a small subset of the data set has this 
status. I have an index on q.status but for some reason this is not 
used. Instead the constraint are ensured with a "Filter: (q.status = 1)" 
in an index scan for the primary key in the "q" table. If the small 
subset having q.status = 1 could be isolated quickly using an index, I 
would expect the query to perform better. I just don't know why the 
planner doesn't use the index on q.status.

In response to

Responses

pgsql-performance by date

Next:From: Pablo AlcarazDate: 2007-11-27 23:06:34
Subject: Re: TB-sized databases
Previous:From: Simon RiggsDate: 2007-11-27 20:57:25
Subject: Re: TB-sized databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group