Re: Setting Statistics on Functional Indexes

On 10/26/2012 02:35 PM, Tom Lane wrote:

> So I'm wondering exactly what "9.1" version you're using, and also
> whether you've got any nondefault planner cost parameters.

Just a plain old 9.1.6 from Ubuntu 12.04. Only thing I personally
changed was the default_statistics_target. Later, I bumped up shared
buffers and work mem, but that just reduced the run time. Still uses the
bad index.

But I just noticed the lag in your response. :) It turns out, even
though I was substituting 2012-10-24 or 2012-10-25, what I really meant
was current_date. That does make all the difference, actually. If the
date in the where clause isn't the current date, it comes up with the
right plan. Even a single day in the past makes it work right. It only
seems to break on the very edge. This should work:

DROP TABLE IF EXISTS date_test;

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) >= current_date
order by id desc, action_date;

Sort (cost=9.39..9.39 rows=1 width=23) (actual time=10.679..10.679
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.01..9.38 rows=1 width=23) (actual time=10.670..10.670 rows=0
loops=1)
Index Cond: (date_trunc('day'::text, action_date) >=
('now'::text)::date)
Filter: (((col1)::text = 'S:96'::text) AND (col2 = 657::numeric))
Total runtime: 10.713 ms

And if this helps:

foo=# select name,setting from pg_settings where setting != boot_val;
name | setting
----------------------------+---------------------
application_name | psql
archive_command | (disabled)
client_encoding | UTF8
default_statistics_target | 500
default_text_search_config | pg_catalog.english
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
lc_messages | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_time | en_US.UTF-8
log_file_mode | 0600
log_line_prefix | %t
max_stack_depth | 2048
server_encoding | UTF8
shared_buffers | 3072
ssl | on
transaction_isolation | read committed
unix_socket_directory | /var/run/postgresql
unix_socket_permissions | 0777
wal_buffers | 96

That's every single setting that's not a default from the compiled PG.
Some of these were obviously modified by Ubuntu, but I didn't touch
anything else. I was trying to produce a clean-room to showcase this.
But I'm seeing it everywhere I test, even with sane settings.

Our EDB server is doing the same thing on much beefier hardware and
correspondingly increased settings, which is what prompted me to test it
in plain PG.

--
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