Message-ID: <42A9D556.7070108@arbash-meinel.com>
Date: Fri, 10 Jun 2005 13:00:54 -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>
In-Reply-To: <20050610132812.R94573@vbp2.vbp2.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig9A20CF5F1BDB9BD858893E30"

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

Clark Slater wrote:
> 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.

Well, if you are matching 130k out of 400k rows, then a sequential scan
is certainly prefered to an index scan. And then you have to sort those
130k rows by partnumber. This *might* be spilling to disk depending on
what your workmem/sortmem is set to.

I would also say that what you would really want is some way to get the
whole thing from an index. And I think the way to do that is:

CREATE INDEX test_partnum_listid_typeid_idx ON
	test(partnumber, productlistid, typeid);

VACUUM ANALYZE test;

EXPLAIN ANALYZE SELECT * FROM test
	WHERE productlistid=3 AND typeid=9
	ORDER BY partnumber, productlistid, typeid
	LIMIT 15
;

The trick is that you have to match the order by exactly with the index,
so the planner realizes it can do an indexed lookup to get the information.

You could also just create an index on partnumber, and see how that
affects your original query. I think the planner could use an index
lookup on partnumber to get the ordering correct. But it will have to do
filtering after the fact based on productlistid and typeid.
With my extended index, I think the planner can be smarter and lookup
all 3 by the index.

> 
> Thanks!
> Clark

Good luck,
John
=:->

--------------enig9A20CF5F1BDB9BD858893E30
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

iD8DBQFCqdVZJdeBCYSNAAMRAi3iAJ99hAuDUUDttUAvzpO7+b96NyEZAACfRUOx
Z+BUvbxWcVRAWDcrABX48so=
=jQ1Z
-----END PGP SIGNATURE-----

--------------enig9A20CF5F1BDB9BD858893E30--