From: | Shaun Thomas <sthomas(at)optionshouse(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Setting Statistics on Functional Indexes |
Date: | 2012-10-24 19:54:52 |
Message-ID: | 5088478C.6000905@optionshouse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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:
CREATE TABLE date_test (
id SERIAL,
col1 varchar,
col2 numeric,
action_date TIMESTAMP WITHOUT TIME ZONE
);
insert into date_test (col1, col2, action_date)
select 'S:' || (a.num % 10000), a.num % 15000,
current_date - a.num % 1000
from generate_series(1,10000000) a(num);
create index idx_date_test_action_date_trunc
on date_test (date_trunc('day', action_date));
create index idx_date_test_col1_col2
on date_test (col1, col2);
set default_statistics_target = 500;
vacuum analyze date_test;
explain analyze
select *
from date_test
where col1 IN ('S:96')
and col2 = 657
and date_trunc('day', action_date) >= '2012-10-24'
order by id desc, action_date;
Sort (cost=9.38..9.39 rows=1 width=23) (actual time=83.418..83.418
rows=0 loops=1)
Sort Key: id, action_date
Sort Method: quicksort Memory: 25kB
-> Index Scan using idx_date_test_action_date_trunc on date_test
(cost=0.00..9.37 rows=1 width=23) (actual time=83.409..83.409 rows=0
loops=1)
Index Cond: (date_trunc('day'::text, action_date) >=
'2012-10-24 00:00:00'::timestamp without time zone)
Filter: (((col1)::text = 'S:96'::text) AND (col2 = 657::numeric))
Total runtime: 83.451 ms
alter index idx_date_test_action_date_trunc
alter column date_trunc set statistics 1000;
analyze date_test;
Sort (cost=9.83..9.83 rows=1 width=23) (actual time=0.077..0.077
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.069..0.069 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.105 m
Then for fun:
create index idx_date_test_action_date_trunc_col1
on date_test (date_trunc('day', action_date), col1);
alter index idx_date_test_action_date_trunc
alter column date_trunc set statistics -1;
analyze date_test;
Sort (cost=9.38..9.39 rows=1 width=23) (actual time=84.375..84.375
rows=0 loops=1)
Sort Key: id, action_date
Sort Method: quicksort Memory: 25kB
-> Index Scan using idx_date_test_action_date_trunc on date_test
(cost=0.00..9.37 rows=1 width=23) (actual time=84.366..84.366 rows=0
loops=1)
Index Cond: (date_trunc('day'::text, action_date) >=
'2012-10-24 00:00:00'::timestamp without time zone)
Filter: (((col1)::text = 'S:96'::text) AND (col2 = 657::numeric))
Total runtime: 84.410 ms
o_O
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas(at)optionshouse(dot)com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-10-24 20:29:57 | Re: Out of shared mem on new box with more mem, 9.1.5 -> 9.1.6 |
Previous Message | Shaun Thomas | 2012-10-24 19:31:11 | Re: Setting Statistics on Functional Indexes |