On Sat, Dec 10, 2016 at 4:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We aren't using any special planner settings - all enable_* options are "on".
>
> No, I'm asking about the cost settings (random_page_cost etc). The cost
> estimates you're showing seem impossible with the default settings.
Tom, really appreciate your pointers. This problem was occurring on
Heroku Postgres databases, and they seem to have set different cost
constants. I tried using SET LOCAL to set them back to the default
settings before running EXPLAIN.
My testing here shows that resetting all of random_page_cost,
cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost does not
change the plan (but does change the cost estimates), while setting
effective_cache_size alone will change the plan.
Specifically, changing only effective_cache_size from '900000kB' to
'4GB' caused the planner to prefer the optimal index
updates_driver_id_time_idx.
Is increasing the DB's RAM the correct fix for this problem? It seems
to me that no matter how much cache is available, looking at the
(driver_id, time) index is always the optimal choice for this query.
Thanks,
Eric
--
Eric Jiang, DoubleMap
eric@doublemap.com | www.doublemap.com