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 0643A5388D for ; Thu, 21 Apr 2005 13:23:37 -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 23488-10 for ; Thu, 21 Apr 2005 16:23:32 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 303CE536A8 for ; Thu, 21 Apr 2005 13:23:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3LGNVMS016776; Thu, 21 Apr 2005 12:23:31 -0400 (EDT) To: Richard van den Berg Cc: pgsql-perform Subject: Re: When are index scans used over seq scans? In-reply-to: <4267D1ED.7010606@trust-factory.com> References: <426665A0.1080102@trust-factory.com> <26710.1114007961@sss.pgh.pa.us> <4267612C.10803@trust-factory.com> <15074.1114093502@sss.pgh.pa.us> <4267D1ED.7010606@trust-factory.com> Comments: In-reply-to Richard van den Berg message dated "Thu, 21 Apr 2005 18:16:45 +0200" Date: Thu, 21 Apr 2005 12:23:31 -0400 Message-ID: <16775.1114100611@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/572 X-Sequence-Number: 11969 Richard van den Berg writes: > Tom Lane wrote: >> Perhaps you are incurring a datatype conversion cost? > Not that I can tell. No, apparently not. Hmm ... timestamp_cmp_internal is just a couple of isnan() checks and one or two floating-point compares. Should be pretty dang cheap. Unless isnan() is ridiculously expensive on your hardware? More likely there is some bottleneck that we are not thinking of. Are the tables in question particularly wide (many columns)? >> which leads me to question what exactly is happening in those >> comparisons. > Your guess is as good as mine (actually, yours is much better). I can > put together a reproducable test case if you like.. I'm thinking it would be interesting to look at a gprof profile of the nestloop case. If you can rebuild with profiling and get one, that would be fine, or you can make up a test case that shows the same slow joining behavior. regards, tom lane