On 10/20/20 6:51 PM, Victor Yegorov wrote:
вт, 20 окт. 2020 г. в 16:50, Mats Olsen <m...@duneanalytics.com
<mailto:m...@duneanalytics.com>>:
On 10/20/20 3:04 PM, Victor Yegorov wrote:
вт, 20 окт. 2020 г. в 11:38, Mats Julian Olsen
<m...@duneanalytics.com <mailto:m...@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
<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)
I'll try to add this.
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.
I'll see if I can add it. This table is huge so normally we only make
changes to these when we redeploy the database.
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
