Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TRpgu-0008Nd-Rx for pgsql-performance@postgresql.org; Fri, 26 Oct 2012 19:35:36 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TRpgq-00027j-7F for pgsql-performance@postgresql.org; Fri, 26 Oct 2012 19:35:36 +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 q9QJZUCg001512; Fri, 26 Oct 2012 15:35:30 -0400 (EDT) From: Tom Lane To: sthomas@optionshouse.com cc: "pgsql-performance@postgresql.org" Subject: Re: Setting Statistics on Functional Indexes In-reply-to: <5088478C.6000905@optionshouse.com> References: <50881D73.100@optionshouse.com> <6668.1351105908@sss.pgh.pa.us> <508841FF.9040303@optionshouse.com> <5088478C.6000905@optionshouse.com> Comments: In-reply-to Shaun Thomas message dated "Wed, 24 Oct 2012 14:54:52 -0500" Date: Fri, 26 Oct 2012 15:35:30 -0400 Message-ID: <1511.1351280130@sss.pgh.pa.us> X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201210/321 X-Sequence-Number: 48280 Shaun Thomas writes: > On 10/24/2012 02:31 PM, Shaun Thomas wrote: >> The main flaw with my example is that it's random. But I swear I'm not >> making it up! :) > And then I find a way to make it non-random. Hooray: I can't reproduce this. In 9.1 for instance, I get Sort (cost=9.83..9.83 rows=1 width=23) (actual time=0.029..0.029 rows=0 loops=1) Sort Key: id, action_date Sort Method: quicksort Memory: 25kB -> Index Scan using idx_date_test_col1_col2 on date_test (cost=0.00..9.82 rows=1 width=23) (actual time=0.021..0.021 rows=0 loops=1) Index Cond: (((col1)::text = 'S:96'::text) AND (col2 = 657::numeric)) Filter: (date_trunc('day'::text, action_date) >= '2012-10-24 00:00:00'::timestamp without time zone) Total runtime: 0.086 ms and those estimates don't change materially with the stats adjustments. If I drop that index altogether, it goes over to this: Sort (cost=521.83..521.83 rows=1 width=23) (actual time=2.544..2.544 rows=0 loops=1) Sort Key: id, action_date Sort Method: quicksort Memory: 25kB -> Index Scan using idx_date_test_action_date_trunc_col1 on date_test (cost=0.00..521.82 rows=1 width=23) (actual time=2.536..2.536 rows=0 loops=1) Index Cond: ((date_trunc('day'::text, action_date) >= '2012-10-24 00:00:00'::timestamp without time zone) AND ((col1)::text = 'S:96'::text)) Filter: (col2 = 657::numeric) Total runtime: 2.600 ms So the planner's conclusions look fairly sane from here. I get about the same results from HEAD, 9.2 branch tip, or 9.1 branch tip. So I'm wondering exactly what "9.1" version you're using, and also whether you've got any nondefault planner cost parameters. regards, tom lane