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.


 id            | integer
 partnumber    | character varying(32)
 productlistid | integer
 typeid        | integer

"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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to