Re: Setting Statistics on Functional Indexes - Mailing list pgsql-performance

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@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email