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 EDB5353A02; Wed, 6 Apr 2005 23:55:58 +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 35524-01; Wed, 6 Apr 2005 22:55:56 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id 14BBE53A35; Wed, 6 Apr 2005 23:55:54 +0100 (BST) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j36MsE304919; Wed, 6 Apr 2005 17:54:14 -0500 Message-ID: <4254688F.6070009@arbash-meinel.com> Date: Wed, 06 Apr 2005 17:54:07 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: "Jim C. Nasby" , Arjen van der Meijden , pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [HACKERS] Recognizing range constraints (was Re: Plan References: <425413D3.5030304@vulcanus.its.tudelft.nl> <28552.1112809334@sss.pgh.pa.us> <425423A9.9000504@vulcanus.its.tudelft.nl> <5999.1112820690@sss.pgh.pa.us> <6326.1112825377@sss.pgh.pa.us> <20050406222536.GL93835@decibel.org> <6594.1112826910@sss.pgh.pa.us> In-Reply-To: <6594.1112826910@sss.pgh.pa.us> 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="------------enig2C897DDE71A01355669582D6" 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/130 X-Sequence-Number: 11527 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig2C897DDE71A01355669582D6 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > "Jim C. Nasby" writes: > >>On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: >> >>>Can anyone suggest a more general rule? Do we need for example to >>>consider whether the relation membership is the same in two clauses >>>that might be opposite sides of a range restriction? It seems like >>> >>>a.x > b.y AND a.x < b.z > > >>In a case like this, you could actually look at the data in b and see >>what the average range size is. > > > Not with the current statistics --- you'd need some kind of cross-column > statistics involving both y and z. (That is, I doubt it would be > helpful to estimate the average range width by taking the difference of > independently-calculated mean values of y and z ...) But yeah, in > principle it would be possible to make a non-default estimate. > > regards, tom lane Actually, I think he was saying do a nested loop, and for each item in the nested loop, re-evaluate if an index or a sequential scan is more efficient. I don't think postgres re-plans once it has started, though you could test this in a plpgsql function. John =:-> --------------enig2C897DDE71A01355669582D6 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.6 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFCVGiPJdeBCYSNAAMRAnEvAKCa0SV2xU5eKMSwpAAERI3Q83A7CQCgt8ig kK5I68eNMy/J1XE8iIz1qlw= =jnsN -----END PGP SIGNATURE----- --------------enig2C897DDE71A01355669582D6--