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 C58CB8B9D05 for ; Tue, 1 Feb 2005 20:07:03 +0000 (GMT) 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 08497-04 for ; Tue, 1 Feb 2005 20:06:52 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 64EB18B9D03 for ; Tue, 1 Feb 2005 20:06:52 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.2/8.12.9) with ESMTP id j11K6nAa011174; (envelope-from ) Tue, 1 Feb 2005 14:06:49 -0600 (CST) Received: from [192.168.1.103] (65-100-23-112.cdrr.qwest.net [65.100.23.112]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j11K6kwv011081 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Tue, 1 Feb 2005 14:06:48 -0600 (CST) Message-ID: <41FFE157.8000708@arbash-meinel.com> Date: Tue, 01 Feb 2005 14:06:47 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joost Kraaijeveld , pgsql-performance@postgresql.org Subject: Re: Why the difference in query plan and performance pg References: In-Reply-To: X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig27EAD94C1E9C3FD9BAF566F0" X-Virus-Scanned: ClamAV 0.80/682/Mon Jan 24 12:53:43 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV 0.80/685/Wed Jan 26 03:08:24 2005, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean 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: 200502/21 X-Sequence-Number: 10350 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig27EAD94C1E9C3FD9BAF566F0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Joost Kraaijeveld wrote: >Hi all, > >I have a freshly vacuumed table with 1104379 records with a index on zipcode. Can anyone explain why the queries go as they go, and why the performance differs so much (1 second versus 64 seconds, or stated differently, 10000 records per second versus 1562 records per second) and why the query plan of query 2 ignores the index? > > > > Indexes are generally only faster if you are grabbing <10% of the table. Otherwise you have the overhead of loading the index into memory, and then paging through it looking for the entries. With 100,000 entries a sequential scan is actually likely to be faster than an indexed one. If you try: select a.ordernumer from orders a order by a.zipcode how long does it take? You can also try disabling sequential scan to see how long Query 2 would be if you used indexing. Remember, though, that because of caching, a repeated index scan may seem faster, but in actual production, that index may not be cached, depending on what other queries are done. John =:-> >For completeness sake I also did a select ordernumber without any ordering. That only took 98 second for 1104379 record (11222 record per second, compariable with the first query as I would have expected). > >Query 1: >select a.ordernumer from orders a order by a.zipcode limit 10000 >Explain: >QUERY PLAN >Limit (cost=0.00..39019.79 rows=10000 width=14) > -> Index Scan using orders_postcode on orders a (cost=0.00..4309264.07 rows=1104379 width=14) >Running time: 1 second > >Query 2: >select a.ordernumer from orders a order by a.zipcode limit 100000 >Explain: >QUERY PLAN >Limit (cost=207589.75..207839.75 rows=100000 width=14) > -> Sort (cost=207589.75..210350.70 rows=1104379 width=14) > Sort Key: postcode > -> Seq Scan on orders a (cost=0.00..46808.79 rows=1104379 width=14) >Running time: 64 seconds > >Query 3: >select a.ordernumer from orders a >QUERY PLAN >Seq Scan on orders a (cost=0.00..46808.79 rows=1104379 width=4) >Running time: 98 seconds > >Groeten, > >Joost Kraaijeveld >Askesis B.V. >Molukkenstraat 14 >6524NB Nijmegen >tel: 024-3888063 / 06-51855277 >fax: 024-3608416 >e-mail: J.Kraaijeveld@Askesis.nl >web: www.askesis.nl > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > --------------enig27EAD94C1E9C3FD9BAF566F0 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.2.4 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB/+FaJdeBCYSNAAMRApOCAJ9Vf8IkMqSS57zDUJOSDoO6gIs7owCgthS8 icNY9AFDdVyLmptZjaM4djE= =Lhu1 -----END PGP SIGNATURE----- --------------enig27EAD94C1E9C3FD9BAF566F0--