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.
