Re: Postgres Optimizer is not smart enough?
От | Mark Kirkwood |
---|---|
Тема | Re: Postgres Optimizer is not smart enough? |
Дата | |
Msg-id | 41E5AF3F.2080100@coretech.co.nz обсуждение исходный текст |
Ответ на | Postgres Optimizer is not smart enough? (Litao Wu <litaowu@yahoo.com>) |
Ответы |
Re: Postgres Optimizer is not smart enough?
Re: Postgres Optimizer is not smart enough? |
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: