From: John A Meinel <john@arbash-meinel.com>
To: Clark Slater <list@slatech.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: faster search
Date: Fri, 10 Jun 2005 19:51:36 -0500
Message-ID: <42AA3598.4080001@arbash-meinel.com> (raw)
In-Reply-To: <20050610200358.R40688@vbp2.vbp2.com>
References: <20050610132812.R94573@vbp2.vbp2.com>
<42A9D556.7070108@arbash-meinel.com>
<20050610200358.R40688@vbp2.vbp2.com>
Clark Slater wrote:
> hmm, i'm baffled. i simplified the query
> and it is still taking forever...
>
>
> test
> -------------------------
> id | integer
> partnumber | character varying(32)
> productlistid | integer
> typeid | integer
>
>
> Indexes:
> "test_productlistid" btree (productlistid)
> "test_typeid" btree (typeid)
> "test_productlistid_typeid" btree (productlistid, typeid)
>
>
> explain analyze select * from test where (productlistid=3 and typeid=9);
>
> QUERY PLAN
> -----------------------------------------------------------------------
> Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual
> time=516.459..41930.250 rows=132528 loops=1)
> Filter: ((productlistid = 3) AND (typeid = 9))
> Total runtime: 41975.154 ms
> (3 rows)
>
>
This query is still going to take a long time, because you have to scan
the whole table. Your WHERE clause is not very specific (it takes 25% of
the table). Convention says that any time you want > 5-10% of a table, a
sequential scan is better, because it does it in order.
Now if you did:
explain analyze select * from test where (productlistid=3 and typeid=9)
limit 15;
I think that would be very fast.
I am a little surprised that it is taking 40s to scan only 400k rows,
though. On an older machine of mine (with only 256M ram and dual 450MHz
Celerons), I have a table with 74k rows which takes about .5 sec. At
those numbers it should take more like 4s not 40.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
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: john@arbash-meinel.com, list@slatech.com
Subject: Re: faster search
In-Reply-To: <42AA3598.4080001@arbash-meinel.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