Message-ID: <42AA3598.4080001@arbash-meinel.com>
Date: Fri, 10 Jun 2005 19:51:36 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2-1.3.3 (X11/20050513)
MIME-Version: 1.0
To: Clark Slater <list@slatech.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: faster search
References: <20050610132812.R94573@vbp2.vbp2.com>
	<42A9D556.7070108@arbash-meinel.com>
	<20050610200358.R40688@vbp2.vbp2.com>
In-Reply-To: <20050610200358.R40688@vbp2.vbp2.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig7ACD2779A4F28991DF6B4A66"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig7ACD2779A4F28991DF6B4A66
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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
=:->

--------------enig7ACD2779A4F28991DF6B4A66
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFCqjWaJdeBCYSNAAMRAiFsAJwLQmLGof86V2lpc5fg0uNL/Fi13wCghqoq
lSp8WQoB+lQQx5YrC0B3sVs=
=bUxz
-----END PGP SIGNATURE-----

--------------enig7ACD2779A4F28991DF6B4A66--