list wrote:

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 (?)



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to