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