On 10/20/20 11:37 AM, Mats Julian Olsen wrote:
Dear Postgres community,
I'm looking for some help to manage queries against two large tables.
Context:
We run a relatively large postgresql instance (5TB, 32 vCPU, 120GB RAM) with a hybrid transactional/analytical workload. Data is written in batches every 15 seconds or so, and the all queryable tables are append-only (we never update or delete). Our users can run analytical queries on top of these tables.
We recently came across a series of troublesome queries one of which I'll dive into here.
The tables in question are:
- `ethereum.transactions`: 833M rows, partitioned, 171M rows after WHERE
- `uniswap_v2."Pair_evt_Swap": 12M rows, not partitioned, 12M rows after WHERE
The query plans I submitted was querying the table `uniswap_v2."Pair_evt_Mint"`which has 560k rows before and after WHERE. Also not partitioned. Apologies for the inconsistency, but as I mentioned the same performance problem holds when using `uniswap_v2."Pair_evt_Swap" (even worse due to it's size).
The crux of our issue is that the query planner chooses a nested loop join for this query. Essentially making this query (and other queries) take a very long time to complete. In contrast, by toggling `enable_nestloop` and `enable_seqscan` off we can take the total runtime down from 16 minutes to 2 minutes.
How can I get Postgres not to loop over 12M rows?
Let me know if there is anything I left out here that would be useful for further debugging.
