From: Clark Slater <list@slatech.com>
To: pgsql-performance@postgresql.org
Subject: faster search
Date: Fri, 10 Jun 2005 13:45:05 -0400 (EDT)
Message-ID: <20050610132812.R94573@vbp2.vbp2.com> (raw)
Hi-
Would someone please enlighten me as
to why I'm not seeing a faster execution
time on the simple scenario below?
there are 412,485 rows in the table and the
query matches on 132,528 rows, taking
almost a minute to execute. vaccuum
analyze was just run.
Thanks!
Clark
test
-------------------------
id | integer
partnumber | character varying(32)
productlistid | integer
typeid | integer
Indexes:
"test_id" btree (id)
"test_plid" btree (productlistid)
"test_typeid" btree (typeid)
"test_plidtypeid" btree (productlistid, typeid)
explain analyze select * from test where productlistid=3 and typeid=9
order by partnumber limit 15;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=201073.76..201073.79 rows=15 width=722) (actual
time=58092.477..58092.518 rows=15 loops=1)
-> Sort (cost=201073.76..201451.76 rows=151200 width=722) (actual
time=58092.470..58092.505 rows=15 loops=1)
Sort Key: partnumber
-> Seq Scan on test (cost=0.00..96458.27 rows=151200 width=722)
(actual time=2.515..40201.275 rows=132528 loops=1)
Filter: ((productlistid = 3) AND (typeid = 9))
Total runtime: 59664.765 ms
(6 rows)
System specs:
PostgreSQL 7.4.2 on RedHat 9
dual AMD Athlon 2GHz processors
1 gig memory
mirrored 7200 RPM IDE disks
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@slatech.com
Subject: Re: faster search
In-Reply-To: <20050610132812.R94573@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