Re: More optimization effort?

On 21 July 2017 at 07:11, Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:

> Currently following query does not use an index:
>
> t-ishii(at)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