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 A13FC3A3F88 for ; Sun, 24 Oct 2004 20:27:59 +0100 (BST) 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 07432-02 for ; Sun, 24 Oct 2004 19:27:23 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 8CF943A3F84 for ; Sun, 24 Oct 2004 20:27:50 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.12.9/8.12.9) with ESMTP id i9OJRvMq028066 for ; (envelope-from ) Sun, 24 Oct 2004 14:27:57 -0500 (CDT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by server11.icaen.uiowa.edu (8.12.9/smtp-service-1.6) with ESMTP id i9OJRvBN000968 for ; (envelope-from ) Sun, 24 Oct 2004 14:27:57 -0500 (CDT) Message-ID: <417C023F.1080502@johnmeinel.com> Date: Sun, 24 Oct 2004 14:27:59 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Sequential Scan with LIMIT X-Enigmail-Version: 0.86.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigF85FC43A9A23B9D3A4C0F238" X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/399 X-Sequence-Number: 8875 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigF85FC43A9A23B9D3A4C0F238 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit I was looking into another problem, and I found something that surprised me. If I'm doing "SELECT * FROM mytable WHERE col = 'myval' LIMIT 1.". Now "col" is indexed, by mytable has 500,000 rows, and 'myval' occurs maybe 100,000 times. Without the LIMIT, this query should definitely do a sequential scan. But with the LIMIT, doesn't it know that it will return at max 1 value, and thus be able to use the index? It seems to be doing the LIMIT too late. The real purpose of this query is to check to see if a value exists in the column, so there might be a better way of doing it. Here is the demo info: # select count(*) from finst_t; 542315 # select count(*) from finst_t where store_id = 539960; 85076 # explain analyze select id from finst_t where store_id = 539960 limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.13 rows=1 width=4) (actual time=860.000..860.000 rows=1 loops=1) -> Seq Scan on finst_t (cost=0.00..11884.94 rows=88217 width=4) (actual time=860.000..860.000 rows=1 loops=1) Filter: (store_id = 539960) Total runtime: 860.000 ms Notice that the "actual rows=1", meaning it is aware of the limit as it is going through the table. But for some reason the planner thinks it is going to return 88,217 rows. (This is close to the reality of 85076 if it actually had to find all of the rows). Now, if I do a select on a value that *does* only have 1 value, it works fine: # explain analyze select id from finst_t where store_id = 9605 limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..3.96 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan using finst_t_store_id_idx on finst_t (cost=0.00..3.96 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (store_id = 9605) Total runtime: 0.000 ms And 1 further thing, I *can* force it to do a fast index scan if I disable sequential scanning. # set enable_seqscan to off; # explain analyze select id from finst_t where store_id = 539960 limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..1.59 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1) -> Index Scan using finst_t_store_id_idx on finst_t (cost=0.00..140417.22 rows=88217 width=4) (actual time=0.000..0.000 rows=1 loops=1) Index Cond: (store_id = 539960) Total runtime: 0.000 ms Could being aware of LIMIT be added to the planner? Is there a better way to check for existence? John =:-> PS> I'm using postgres 8.0-beta3 on win32 (the latest installer). --------------enigF85FC43A9A23B9D3A4C0F238 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 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBfAI/JdeBCYSNAAMRAi+UAKDFkbJ9JSV6NIJjyii2oPDIMaWj+ACfV4Nv NnPsyuCflirLpwD6pD7YxHs= =UkRy -----END PGP SIGNATURE----- --------------enigF85FC43A9A23B9D3A4C0F238--