Re: Query Performance / Planner estimate off

вт, 20 окт. 2020 г. в 16:50, Mats Olsen <mats@duneanalytics.com>:

On 10/20/20 3:04 PM, Victor Yegorov wrote:

вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen <mats@duneanalytics.com>:

I'm looking for some help to manage queries against two large tables.

Can you tell the version you're running currently and the output of this query, please?

    select name,setting,source from pg_settings where source not in ('default','override'); 

Running "PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit"

Updated the gist to include the results forom pg_settings. Here's the direct link https://gist.githubusercontent.com/mewwts/9f11ae5e6a5951593b8999559f5418cf/raw/e5deebbbb48680e04570bec4e9a816fa009da34f/pg_settings

It looks like indexes currently chosen by the planner don't quite fit your query.

I would create the following index (if it's possible to update schema):
   ON "uniswap_v2.Pair_evt_Mint" (evt_tx_hash, evt_block_time)

Same for the second table, looks like
  ON "ethereum.transactions" (hash, block_time)
is a better fit for your query. In fact, I do not think `transactions_block_number_time` index is used frequently, 'cos second column of the index is a partitioning key.

Currently planner wants to go via indexes 'cos you've made random access really cheap compared to sequential one (and your findings shows this).
Perhaps on a NVMe disks this could work, but in your case you need to find the real bottleneck (therefore I asked for buffers).

I would set `random_page_cost` to a 2.5 at least with your numbers. Also, I would check DB and indexes for bloat (just a guess now, 'cos your plans miss buffers figures)

Yeah, 1.1 seems way to low.

Here's the output of the explain (analyze, buffers, settings) you asked for:

vanilla: https://explain.depesz.com/s/Ktrd

set enable_nestloop=off: https://explain.depesz.com/s/mvSD

set enable_nestloop=off; set enable_seqscan=off: https://explain.depesz.com/s/XIDo

--

Victor Yegorov