Re: [HACKERS] More optimization effort?

Поиск
Список
Период
Сортировка
От Craig Ringer
Тема Re: [HACKERS] More optimization effort?
Дата
Msg-id CAMsr+YFCc1YU4uvADVyKgzkP8wUdOhiEj2tMggGAL02J5b4qTA@mail.gmail.com
обсуждение исходный текст
Ответ на [HACKERS] More optimization effort?  (Tatsuo Ishii <ishii@sraoss.co.jp>)
Список pgsql-hackers
On 21 July 2017 at 07:11, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
Currently following query does not use an index:

t-ishii@localhost: psql -p 5433 test
Pager usage is off.
psql (9.6.3)
Type "help" for help.

test=# explain select * from pgbench_accounts where aid*100 < 10000;
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..3319.00 rows=33333 width=97)
   Filter: ((aid * 100) < 10000)
(2 rows)

While following one does use the index.

test=# explain select * from pgbench_accounts where aid < 10000/100;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.29..11.08 rows=102 width=97)
   Index Cond: (aid < 100)
(2 rows)

Is it worth to make our optimizer a little bit smarter to convert the
the first query into the second form?

If I understand correctly, you're proposing that the optimiser should attempt algebraic simplification to fold more constants, rather than stopping pre-evaluation constant expressions  as soon as we see a non-constant like we do now. Right?

I'm sure there are documented algorithms out there for algebraic manipulations like that, taking account of precedence etc. But will they be cheap enough to run in the optimiser? And likely to benefit many queries? 

There's also the hiccup of partial index matching. If Pg simplifies and rearranges expressions more, will we potentially fail to match partial indexes that we would've originally matched? I'm not sure it's a blocker, but it bears consideration, and Pg might have to do more work on partial index matching too.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: [HACKERS] Better error message for trying to drop a DB with open subscriptions?
Следующее
От: Craig Ringer
Дата:
Сообщение: Re: [HACKERS] Definitional questions for pg_sequences view