X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D8AD652871 for ; Fri, 10 Jun 2005 14:45:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98787-01 for ; Fri, 10 Jun 2005 17:45:09 +0000 (GMT) Received: from vbp2.vbp2.com (vbp2.com [66.207.137.158]) by svr1.postgresql.org (Postfix) with ESMTP id 4F70C52932 for ; Fri, 10 Jun 2005 14:45:08 -0300 (ADT) Received: from vbp2.vbp2.com (localhost [127.0.0.1]) by vbp2.vbp2.com (8.13.1/8.13.1) with ESMTP id j5AHj6ZT019804 for ; Fri, 10 Jun 2005 13:45:06 -0400 (EDT) (envelope-from list@slatech.com) Received: from localhost (list@localhost) by vbp2.vbp2.com (8.13.1/8.13.1/Submit) with ESMTP id j5AHj5EP019801 for ; Fri, 10 Jun 2005 13:45:06 -0400 (EDT) (envelope-from list@slatech.com) X-Authentication-Warning: vbp2.vbp2.com: list owned process doing -bs Date: Fri, 10 Jun 2005 13:45:05 -0400 (EDT) From: Clark Slater X-X-Sender: list@vbp2.vbp2.com To: pgsql-performance@postgresql.org Subject: faster search Message-ID: <20050610132812.R94573@vbp2.vbp2.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/243 X-Sequence-Number: 12880 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