From: Tom Lane <tgl@sss.pgh.pa.us>
To: sthomas@optionshouse.com
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Setting Statistics on Functional Indexes
Date: Fri, 26 Oct 2012 15:35:30 -0400
Message-ID: <1511.1351280130@sss.pgh.pa.us> (raw)
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>
Shaun Thomas <sthomas@optionshouse.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sthomas@optionshouse.com
Subject: Re: Setting Statistics on Functional Indexes
In-Reply-To: <1511.1351280130@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox