Re: Slow join query

On Jun 22, 2007, at 13:32 , Tom Tamulewicz wrote:
> ( p.void_flag IS NULL OR p.void_flag = false )
Just a note: you can rewrite (a IS NULL or a = false) as (a IS NOT
TRUE). Shouldn't affect performance, but might make your query easier
to read.

What's the EXPLAIN ANALYZE output for this query?
> When the query runs, the hard drive lights up for the duration.
> (I'm confused by this as 'top' reports only 24k of swap in use).
> My SUSE 9 test machine has 512 Meg of RAM with 300 Meg used by a
> Java app.  Postmaster reports 56 Meg under "top" and has a 52 Meg
> segment under "ipcs".  I've played with the cache size, shared
> buffers, and OS shmmax with little change in the query performance.
>
> Q: Would this query benefit from using a view between these two
> tables?
I doubt it, as views are just pre-parsed queries: no data is
materialized for the view.
> Q: Any idea why the reported swap usage is so low, yet the query
> slams the drive?  Is postgres not caching this data?  If I run the
> query with the same arguments, it comes right back the second
> time.  If I change the args and re-run, it goes back to the hard
> drive and takes 30-50 seconds.
How much is cached depends on shared_buffers, I believe. If the
result is still cached, that'd explain why running the query with the
same arguments returns so quickly. You might see some improvement
using a prepared query, as the server shouldn't have to reparse and
replan the query. Of course, if you change the arguments, it can't
use the result that's cached from the previous run.

Take this all with an appropriate amount of salt. I'm learning about
this, too.

Michael Glaesemann
grzm seespotcode net