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

Re: tuning

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: list <list(at)vbp2(dot)vbp2(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: tuning
Date: 2005-05-30 22:41:59
Message-ID: 429B96B7.2050502@paradise.net.nz (view raw or flat)
Thread:
Lists: pgsql-performance
list wrote:
> hi-
> 
> i would like to see if someone could recommend something
> to make my query run faster.
> 
> 
> Values in postgresql.conf:
> shared_buffers = 1000
> sort_mem is commented out
> effective_cache_size is commented out
> random_page_cost is commented out
> 

I would increase shared_buffers (say 5000 - 10000), and also 
effective_cache_size (say around 20000 - 50000 - but work out how much 
memory this box has free or cached and adjust accordingly).

 From your explain output, it looks like sorting is not too much of a 
problem - so you can leave it unchanged (for this query anyway).

> Here is the query in question:
> select * from productvendorview where (productlistid=3 or 
> productlistid=5 or productlistid=4) and (lower(item) like '9229%' or 
> lower(descrip) like 'toner%') order by vendorname,item limit 100;
>

You might want to break this into 2 queries and union them, so you can 
(potentially) use the indexes on productlistid,lower(item) and 
productlistid, lower(descrip) separately.


> This query scans 412,457 records.
> 
> Here is the EXPLAIN ANALYZE for the query:
> 
>  Limit  (cost=45718.83..45719.08 rows=100 width=108) (actual 
> time=39093.636..39093.708 rows=100 loops=1)
>    ->  Sort  (cost=45718.83..45727.48 rows=3458 width=108) (actual 
> time=39093.629..39093.655 rows=100 loops=1)
>          Sort Key: v.vendorname, p.item
>          ->  Hash Join  (cost=22.50..45515.57 rows=3458 width=108) 
> (actual time=95.490..39062.927 rows=2440 loops=1)
>                Hash Cond: ("outer".vendorid = "inner".id)
>                ->  Seq Scan on test p  (cost=0.00..45432.57 rows=3457 
> width=62) (actual time=89.066..39041.654 rows=2444 loops=1)
>                      Filter: (((productlistid = 3) OR (productlistid = 
> 5) OR (productlistid = 4)) AND
>                               ((lower((item)::text) ~~ '9229%'::text) OR 
> (lower((descrip)::text) ~~ 'toner%'::text)))
>                ->  Hash  (cost=20.00..20.00 rows=1000 width=54) (actual 
> time=6.289..6.289 rows=0 loops=1)
>                      ->  Seq Scan on vendor v  (cost=0.00..20.00 
> rows=1000 width=54) (actual time=0.060..3.653 rows=2797 loops=1)
>  Total runtime: 39094.713 ms
> (10 rows)
> 

I guess the relation 'test' is a copy of product (?)

Cheers

Mark



In response to

  • tuning at 2005-05-26 20:54:45 from list

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2005-05-31 01:52:26
Subject: Re: poor performance involving a small table
Previous:From: Rudi StarcevicDate: 2005-05-30 22:16:49
Subject: Re: slow queries, possibly disk io

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