Re: Postgres Optimizer is not smart enough?

Litao  Wu Wrote:
> explain analyze
> SELECT module,  sum(action_deny)
> FROM test
> WHERE  created >= ('now'::timestamptz - '1
> day'::interval) AND customer_id='100'
>   AND  domain='100'
> GROUP BY module;

Here is my output for this query:

                                                               QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=3.03..3.03 rows=1 width=13) (actual
time=0.132..0.135 rows=1 loops=1)
    ->  Index Scan using test_id2 on test  (cost=0.00..3.02 rows=1
width=13) (actual time=0.085..0.096 rows=1 loops=1)
          Index Cond: (("domain")::text = '100'::text)
          Filter: ((created >= ('2005-01-13
11:57:34.673833+13'::timestamp with time zone - '1 day'::interval)) AND
(customer_id = 100))
  Total runtime: 0.337 ms
(5 rows)

Time: 8.424 ms


The version is:
PostgreSQL 8.0.0rc5 on i386-unknown-freebsd5.3, compiled by GCC gcc
(GCC) 3.4.2 [FreeBSD] 20040728


I have random_page_cost = 0.8 in my postgresql.conf. Setting it back to
the default (4) results in a plan using test_id1. A little
experimentation showed that for my system random_page_cost=1 was where
it changed from using test_id1 to test_id2.

So changing this parameter may be helpful.

I happen to have some debugging code enabled for the optimizer, and the
issue appears to be that the costs of paths using these indexes are
quite similar, so are quite sensitive to (some) parameter values.

regards

Mark

P.s : 7.3.2 is quite old.