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 60B485513E for ; Tue, 8 Mar 2005 19:01:39 +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 62610-10 for ; Tue, 8 Mar 2005 19:01:35 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id C713656446 for ; Tue, 8 Mar 2005 19:01:30 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j28J1Orb019402; (envelope-from ) Tue, 8 Mar 2005 13:01:24 -0600 (CST) Received: from [192.168.1.13] (63-230-159-69.cdrr.qwest.net [63.230.159.69]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j28J1LcD000850 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Tue, 8 Mar 2005 13:01:23 -0600 (CST) Message-ID: <422DF680.1000409@arbash-meinel.com> Date: Tue, 08 Mar 2005 13:01:20 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rick Schumeyer Cc: pgsql-performance@postgresql.org Subject: Re: index scan on =, but not < ? References: <001f01c5240d$a920c8c0$0200a8c0@dell8200> In-Reply-To: <001f01c5240d$a920c8c0$0200a8c0@dell8200> 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="------------enig3995DA4B113C496627888CFC" Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 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: 200503/134 X-Sequence-Number: 10839 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig3995DA4B113C496627888CFC Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Rick Schumeyer wrote: > I have two index questions. The first is about an issue that has been > recently discussed, > > and I just wanted to be sure of my understanding. Functions like > count(), max(), etc. will > > use sequential scans instead of index scans because the index doesn’t > know which rows > > are actually visible…is this correct? > Actually, index scans are chosen whenever the cost is expected to be cheaper than a sequential scan. This is generally about < 10% of the total number of rows. > Second: > > I created an index in a table with over 10 million rows. > > The index is on field x, which is a double. > > The following command, as I expected, results in an index scan: > > =# explain select * from data where x = 0; > > QUERY PLAN > > ------------------------------------------------------------------------- > > Index Scan using data_x_ix on data (cost=0.00..78.25 rows=19 width=34) > > Index Cond: (x = 0::double precision) > > (2 rows) > Since you have 10m rows, when it expects to get only 19 rows, it is much faster to use an index. > But this command, in which the only difference if > instead of =, is a > sequential scan. > > =# explain select * from data where x > 0; > > QUERY PLAN > > ------------------------------------------------------------------ > > Seq Scan on data (cost=0.00..1722605.20 rows=62350411 width=34) > > Filter: (x > 0::double precision) > > (2 rows) > Here, pg expects to find 62M rows (you must have significantly more than 10M rows). In this case a sequential scan is much faster than an indexed one, so that's what pg does. > Why is this? > > (This is with pg 8.0.1 on a PC running FC3 with 1GB ram…if it matters) > If you think there is truly a performance problem, try attaching the results of "explain analyze" in which we might be able to tell you that your statistics inaccurate (run vacuum analyze if you haven't). John =:-> --------------enig3995DA4B113C496627888CFC 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.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCLfaAJdeBCYSNAAMRArIOAJsFE/b+GjmlClMhtYb8oESRYEFF+gCgjh0U NYwh/iFa4Oj4omGTUvRFLKc= =W/9K -----END PGP SIGNATURE----- --------------enig3995DA4B113C496627888CFC--