ethereum.transactions

                                    Partitioned table "ethereum.transactions"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 block_time   | timestamp with time zone |           | not null |         | plain    |              | 
 nonce        | numeric                  |           | not null |         | main     |              | 
 index        | numeric                  |           | not null |         | main     |              | 
 success      | boolean                  |           |          |         | plain    |              | 
 from         | bytea                    |           | not null |         | extended |              | 
 to           | bytea                    |           |          |         | extended |              | 
 value        | numeric                  |           | not null |         | main     |              | 
 block_number | bigint                   |           | not null |         | plain    |              | 
 block_hash   | bytea                    |           | not null |         | extended |              | 
 gas_limit    | numeric                  |           | not null |         | main     |              | 
 gas_price    | numeric                  |           | not null |         | main     |              | 
 gas_used     | numeric                  |           | not null |         | main     |              | 
 data         | bytea                    |           |          |         | extended |              | 
 hash         | bytea                    |           | not null |         | extended |              | 
Partition key: RANGE (block_number)
Indexes:
    "transactions_block_number_btree" btree (block_number DESC)
    "transactions_block_number_hash_key" UNIQUE CONSTRAINT, btree (block_number, hash)
    "transactions_block_number_time" btree (hash, block_number)
    "transactions_block_time" btree (block_time)
    "transactions_from" btree ("from")
    "transactions_hash" btree (hash)
    "transactions_to" btree ("to")
Partitions: ethereum.transactions_p0 FOR VALUES FROM ('0') TO ('500000'),
            ethereum.transactions_p1000000 FOR VALUES FROM ('1000000') TO ('1500000'),
            ethereum.transactions_p10000000 FOR VALUES FROM ('10000000') TO ('10500000'),
            ethereum.transactions_p10500000 FOR VALUES FROM ('10500000') TO ('11000000'),
            ethereum.transactions_p11000000 FOR VALUES FROM ('11000000') TO ('11500000'),
            ethereum.transactions_p11500000 FOR VALUES FROM ('11500000') TO ('12000000'),
            ethereum.transactions_p12000000 FOR VALUES FROM ('12000000') TO ('12500000'),
            ethereum.transactions_p1500000 FOR VALUES FROM ('1500000') TO ('2000000'),
            ethereum.transactions_p2000000 FOR VALUES FROM ('2000000') TO ('2500000'),
            ethereum.transactions_p2500000 FOR VALUES FROM ('2500000') TO ('3000000'),
            ethereum.transactions_p3000000 FOR VALUES FROM ('3000000') TO ('3500000'),
            ethereum.transactions_p3500000 FOR VALUES FROM ('3500000') TO ('4000000'),
            ethereum.transactions_p4000000 FOR VALUES FROM ('4000000') TO ('4500000'),
            ethereum.transactions_p4500000 FOR VALUES FROM ('4500000') TO ('5000000'),
            ethereum.transactions_p500000 FOR VALUES FROM ('500000') TO ('1000000'),
            ethereum.transactions_p5000000 FOR VALUES FROM ('5000000') TO ('5500000'),
            ethereum.transactions_p5500000 FOR VALUES FROM ('5500000') TO ('6000000'),
            ethereum.transactions_p6000000 FOR VALUES FROM ('6000000') TO ('6500000'),
            ethereum.transactions_p6500000 FOR VALUES FROM ('6500000') TO ('7000000'),
            ethereum.transactions_p7000000 FOR VALUES FROM ('7000000') TO ('7500000'),
            ethereum.transactions_p7500000 FOR VALUES FROM ('7500000') TO ('8000000'),
            ethereum.transactions_p8000000 FOR VALUES FROM ('8000000') TO ('8500000'),
            ethereum.transactions_p8500000 FOR VALUES FROM ('8500000') TO ('9000000'),
            ethereum.transactions_p9000000 FOR VALUES FROM ('9000000') TO ('9500000'),
            ethereum.transactions_p9500000 FOR VALUES FROM ('9500000') TO ('10000000'),
            ethereum.transactions_default DEFAULT

pg_settings

application_name	psql	session
cluster_name	12/main	configuration file
cron.database_name	dune	configuration file
DateStyle	ISO, MDY	configuration file
default_statistics_target	500	configuration file
default_text_search_config	pg_catalog.english	configuration file
dynamic_shared_memory_type	posix	configuration file
effective_cache_size	7864320	configuration file
effective_io_concurrency	200	configuration file
external_pid_file	/var/run/postgresql/12-main.pid	configuration file
lc_messages	C	configuration file
lc_monetary	C.UTF-8	configuration file
lc_numeric	C.UTF-8	configuration file
lc_time	C.UTF-8	configuration file
listen_addresses	*	configuration file
log_autovacuum_min_duration	0	configuration file
log_checkpoints	on	configuration file
log_connections	on	configuration file
log_disconnections	on	configuration file
log_error_verbosity	default	configuration file
log_line_prefix	%m [%p] %q%u@%d 	configuration file
log_lock_waits	on	configuration file
log_min_duration_statement	300000	configuration file
log_rotation_age	1440	configuration file
log_temp_files	0	configuration file
log_timezone	Etc/UTC	configuration file
log_truncate_on_rotation	on	configuration file
maintenance_work_mem	8388608	user
max_connections	100	configuration file
max_locks_per_transaction	128	configuration file
max_parallel_maintenance_workers	4	configuration file
max_parallel_workers_per_gather	4	configuration file
max_stack_depth	2048	environment variable
max_wal_size	8192	configuration file
max_worker_processes	4	configuration file
min_wal_size	2048	configuration file
pg_partman_bgw.dbname	dune	configuration file
pg_partman_bgw.interval	86400	configuration file
pg_partman_bgw.role	partman	configuration file
port	5432	configuration file
random_page_cost	1.1	configuration file
shared_buffers	2621440	configuration file
shared_preload_libraries	pg_partman_bgw,pg_cron	configuration file
stats_temp_directory	/var/run/postgresql/pg_stat_tmp_ram	configuration file
superuser_reserved_connections	2	configuration file
TimeZone	Etc/UTC	configuration file
unix_socket_directories	/var/run/postgresql	configuration file
wal_buffers	2048	configuration file
work_mem	57344	configuration file

postgresql.conf

work_mem = '56MB'
effective_cache_size = '60GB'
effective_io_concurrency = 200
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
shared_buffers = '20GB'
random_page_cost = 1.1

query.sql

SELECT
  "from" as LP,
  count(*) as mints
FROM uniswap_v2."Pair_evt_Mint" m
INNER JOIN ethereum."transactions" tx ON m.evt_tx_hash = tx.hash
WHERE block_time > '5/1/20'
AND evt_block_time > '5/1/20'
GROUP BY 1
ORDER BY 2 desc
LIMIT 100

stats Mint

dunev5=# SELECT attname, inherited, n_distinct
FROM pg_stats
WHERE tablename = 'Pair_evt_Mint';
     attname      | inherited | n_distinct  
------------------+-----------+-------------
 sender           | f         |          63
 amount0          | f         | -0.86164504
 amount1          | f         | -0.87452465
 contract_address | f         |       16786
 evt_tx_hash      | f         | -0.99794143
 evt_index        | f         |         576
 evt_block_time   | f         | -0.56466025
 evt_block_number | f         | -0.56466025
 sender           | f         |           2
 amount0          | f         |  -0.9333791
 amount1          | f         |  -0.9364092
 contract_address | f         |         182
 evt_tx_hash      | f         |          -1
 evt_index        | f         |         416
 evt_block_time   | f         |  -0.8379525
 evt_block_number | f         |  -0.8379525
(16 rows)

stats transactions

dunev5=# SELECT attname, inherited, n_distinct
FROM pg_stats
WHERE tablename = 'transactions';
   attname    | inherited | n_distinct  
--------------+-----------+-------------
 block_time   | t         |      741843
 nonce        | t         |      301528
 index        | t         |         495
 success      | t         |           2
 from         | t         |      662673
 to           | t         |      416528
 value        | t         |      369268
 block_number | t         |      741843
 block_hash   | t         |      741843
 gas_limit    | t         |       56820
 gas_price    | t         |       42947
 gas_used     | t         |       74212
 data         | t         | 2.29023e+06
 hash         | t         |          -1
(14 rows)

uniswap_v2.Pair_evt_Mint

                                           Table "uniswap_v2.Pair_evt_Mint"
      Column      |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 sender           | bytea                    |           |          |         | extended |              | 
 amount0          | numeric                  |           |          |         | main     |              | 
 amount1          | numeric                  |           |          |         | main     |              | 
 contract_address | bytea                    |           | not null |         | extended |              | 
 evt_tx_hash      | bytea                    |           | not null |         | extended |              | 
 evt_index        | bigint                   |           | not null |         | plain    |              | 
 evt_block_time   | timestamp with time zone |           | not null |         | plain    | 10000        | 
 evt_block_number | bigint                   |           | not null |         | plain    |              | 
Indexes:
    "Pair_evt_Mint_pkey" PRIMARY KEY, btree (evt_tx_hash, evt_index)
    "Pair_evt_Mint_evt_block_time_idx" btree (evt_block_time)
Statistics objects:
    "uniswap_v2"."time_stat" (ndistinct, dependencies, mcv) ON evt_tx_hash, evt_block_time FROM uniswap_v2."Pair_evt_Mint"
Access method: heap