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 785F953860 for ; Wed, 20 Apr 2005 12:37:40 -0300 (ADT) 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 48486-02 for ; Wed, 20 Apr 2005 15:37:39 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 9E99753658 for ; Wed, 20 Apr 2005 12:37:35 -0300 (ADT) 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 j3KFbY6h024347; (envelope-from ) Wed, 20 Apr 2005 10:37:34 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3KFbXAo021531 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 20 Apr 2005 10:37:34 -0500 (CDT) Message-ID: <4266773E.405@arbash-meinel.com> Date: Wed, 20 Apr 2005 10:37:34 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Richard van den Berg Cc: pgsql-perform Subject: Re: When are index scans used over seq scans? References: <426665A0.1080102@trust-factory.com> <42666890.1000401@arbash-meinel.com> <42667219.7050907@trust-factory.com> In-Reply-To: <42667219.7050907@trust-factory.com> X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig4BF4BA191A2E5D803529C098" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 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-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: 200504/504 X-Sequence-Number: 11901 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig4BF4BA191A2E5D803529C098 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Richard van den Berg wrote: >John A Meinel wrote: > > >>I believe the problem is that postgres doesn't recognize how restrictive >>a date-range is unless it uses constants. >> >> > >And it does when using BETWEEN with int for example? Impressive. :-) > > > >>select blah from du WHERE time between '2004-10-10' and '2004-10-15'; >>Will properly use the index, because it realizes it only returns a few >>rows. >> >> > >Correct, it does. > > > >>Probably you should try to find out the status of multi-table >>selectivity. It was discussed in the last couple of months. >> >> > >I can't find the posts you are refering to. What is the priciple of >multi-table selectivity? > >Your explanation sounds very plausible.. I don't mind changing the >cpu_tuple_cost before running BETWEEN with timestamps, they are easy >enough to spot. > >Thanks, > > > Well, there was a thread titled "date - range" There is also "recognizing range constraints" which started with "plan for relatively simple query seems to be very inefficient". Sorry that I gave you poor search terms. Anyway, "date - range" gives an interesting workaround. Basically you store date ranges with a different structure, which allows fast index lookups. The other threads are just discussing the possibility of improving the planner so that it recognizes WHERE a > b AND a < c, is generally more restrictive. There was a discussion about how to estimate selectivity, but I think it mostly boils down that except for pathological cases, a > b AND a < c is always more restrictive than just a > b, or a < c. Some of it may be also be found in pgsql-hackers, rather than pgsql-performance, but I'm not subscribed to -hackers, so most of it should be in -performance. John =:-> caveat, I'm not a developer, I just read a lot of the list. --------------enig4BF4BA191A2E5D803529C098 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 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZnc+JdeBCYSNAAMRAnSDAKDFK/HalLyx+EL7p+rtNXgamXNzHQCgnoOk 2GDq+APGlAJVsy0Gs1J6Y0I= =4kZT -----END PGP SIGNATURE----- --------------enig4BF4BA191A2E5D803529C098--