From: list <list@vbp2.vbp2.com>
To: pgsql-performance@postgresql.org
Subject: tuning
Date: Thu, 26 May 2005 16:54:45 -0400 (EDT)
Message-ID: <20050526164228.S10655@vbp2.vbp2.com> (raw)
hi-
i would like to see if someone could recommend something
to make my query run faster.
System specs:
PostgreSQL 7.4.2 on RedHat 9
dual AMD Athlon 2GHz processors
1 gig memory
mirrored 7200 RPM IDE disks
Values in postgresql.conf:
shared_buffers = 1000
sort_mem is commented out
effective_cache_size is commented out
random_page_cost is commented out
Relevant tables:
product
-------
id serial
productlistid integer
vendorid integer
item varchar(32)
descrip varchar(256)
price double
vendor
------
id serial
vendorname varchar(64)
A view i made in order to easily retrieve the vendor name:
create view productvendorview as select p.id, p.productlistid,
v.vendorname, p.item, p.descrip, p.price from product p, vendor v where
p.vendorid = v.id;
Here are some indices i have created:
create index product_plid on product (productlistid);
create index product_plidloweritem on product (productlistid, lower(item) varchar_pattern_ops);
create index product_plidlowerdescrip on product (productlistid, lower(descrip) varchar_pattern_ops);
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;
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)
Thanks!
-Clark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: list@vbp2.vbp2.com
Subject: Re: tuning
In-Reply-To: <20050526164228.S10655@vbp2.vbp2.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox