Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THhHX-0005Hj-OA for pgsql-performance@postgresql.org; Fri, 28 Sep 2012 20:35:31 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THhHT-0008KM-UP for pgsql-performance@postgresql.org; Fri, 28 Sep 2012 20:35:31 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q8SKZPVB014849; Fri, 28 Sep 2012 16:35:25 -0400 (EDT) From: Tom Lane To: sthomas@optionshouse.com cc: "pgsql-performance@postgresql.org" Subject: Re: Possible Performance Regression with Transitive Comparisons vs. Constants In-reply-to: <5065F910.5040504@optionshouse.com> References: <5065F910.5040504@optionshouse.com> Comments: In-reply-to Shaun Thomas message dated "Fri, 28 Sep 2012 14:22:56 -0500" Date: Fri, 28 Sep 2012 16:35:25 -0400 Message-ID: <14848.1348864525@sss.pgh.pa.us> X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201209/201 X-Sequence-Number: 47947 Shaun Thomas writes: > I ran into this while we were working on an upgrade project. We're > moving from 8.2 (don't ask) to 9.1, and started getting terrible > performance for some queries. I've managed to boil it down to a test case: 9.1.what? For me, 8.2.23 and 9.1.6 produce the same plan and just about the same runtime for your query 1. For query 2, 9.1.6 prefers to stick in a Materialize node, which cuts the runtime 30% or so --- but if I set enable_material to off then I get the same plan and runtime as with 8.2. Perhaps you should show the EXPLAIN ANALYZE outputs you're actually getting, rather than assuming others will get the same thing. regards, tom lane (PS: it does seem that HEAD has got some kind of issue here, because it's picking a plain not bitmap indexscan. I'll go look at that. But I don't see that misbehavior in 9.1.)