I have few tables will 20-40million rows, due to which my queries used to take a lot of time for execution. Are there any suggestions to troubleshoot/analyze the queries in details as of where most of the memory is consumed or any more suggestions before going for partitioning?
Also, I have few queries which are used for analysis too, and these queries run over whole range of dates (have to go through whole data).
So I will need an overall solution to keep my basic queries fast and that the analysis queries doesn't fail by going out of memory or crashing the DB.
One table size is nearly 120GB, other tables just have huge number of rows. I tried to partition the tables with weekly and monthly date basis but then the queries are running out of memory, number of locks increases by a huge factor while having partitions, normal table query took 13 locks and queries on partitioned tables take 250 locks (monthly partition) and 1000 locks (weekly partitions). I read, there is an overhead that adds up while we have partitions.
Analysis query:
SELECT id
from TABLE1
where id NOT IN (
SELECT DISTINCT id
FROM TABLE2
);
TABLE1 and TABLE2 are partitioned, the first by event_data_timestamp and the second by event_timestamp.
Analysis queries run out of memory and consumes huge number of locks, date based queries are pretty fast though.
QUERY:
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM Table1_monthly WHERE event_timestamp > '2019-01-01' and id NOT IN (SELECT DISTINCT id FROM Table2_monthly where event_data_timestamp > '2019-01-01');
Append (cost=32731.14..653650.98 rows=4656735 width=16) (actual time=2497.747..15405.447 rows=10121827 loops=1)
Buffers: shared hit=3 read=169100
-> Seq Scan on TABLE1_monthly_2019_01_26 (cost=32731.14..77010.63 rows=683809 width=16) (actual time=2497.746..3489.767 rows=1156382 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 462851
Buffers: shared read=44559
SubPlan 1
-> HashAggregate (cost=32728.64..32730.64 rows=200 width=16) (actual time=248.084..791.054 rows=1314570 loops=6)
Group Key: TABLE2_monthly_2019_01_26.cid
Buffers: shared read=24568
-> Append (cost=0.00..32277.49 rows=180458 width=16) (actual time=22.969..766.903 rows=1314570 loops=1)
Buffers: shared read=24568
-> Seq Scan on TABLE2_monthly_2019_01_26 (cost=0.00..5587.05 rows=32135 width=16) (actual time=22.965..123.734 rows=211977 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 40282
Buffers: shared read=4382
-> Seq Scan on TABLE2_monthly_2019_02_25 (cost=0.00..5573.02 rows=32054 width=16) (actual time=0.700..121.657 rows=241977 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Buffers: shared read=4371
-> Seq Scan on TABLE2_monthly_2019_03_27 (cost=0.00..5997.60 rows=34496 width=16) (actual time=0.884..123.043 rows=253901 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Buffers: shared read=4704
-> Seq Scan on TABLE2_monthly_2019_04_26 (cost=0.00..6581.55 rows=37855 width=16) (actual time=0.690..129.537 rows=282282 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Buffers: shared read=5162
-> Seq Scan on TABLE2_monthly_2019_05_26 (cost=0.00..6585.38 rows=37877 width=16) (actual time=1.248..122.794 rows=281553 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Buffers: shared read=5165
-> Seq Scan on TABLE2_monthly_2019_06_25 (cost=0.00..999.60 rows=5749 width=16) (actual time=0.750..23.020 rows=42880 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
Buffers: shared read=784
-> Seq Scan on TABLE2_monthly_2019_07_25 (cost=0.00..12.75 rows=73 width=16) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
-> Seq Scan on TABLE2_monthly_2019_08_24 (cost=0.00..12.75 rows=73 width=16) (actual time=0.003..0.004 rows=0 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
-> Seq Scan on TABLE2_monthly_2019_09_23 (cost=0.00..12.75 rows=73 width=16) (actual time=0.003..0.004 rows=0 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
-> Seq Scan on TABLE2_monthly_2019_10_23 (cost=0.00..12.75 rows=73 width=16) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (event_data_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone)
-> Seq Scan on TABLE1_monthly_2019_02_25 (cost=32731.14..88679.16 rows=1022968 width=16) (actual time=1008.738..2341.807 rows=1803957 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 241978
Buffers: shared hit=1 read=25258
-> Seq Scan on TABLE1_monthly_2019_03_27 (cost=32731.14..97503.58 rows=1184315 width=16) (actual time=1000.795..2474.769 rows=2114729 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 253901
Buffers: shared hit=1 read=29242
-> Seq Scan on TABLE1_monthly_2019_04_26 (cost=32731.14..105933.54 rows=1338447 width=16) (actual time=892.820..2405.941 rows=2394619 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 282282
Buffers: shared hit=1 read=33048
-> Seq Scan on TABLE1_monthly_2019_05_26 (cost=32731.14..87789.65 rows=249772 width=16) (actual time=918.397..2614.059 rows=2340789 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 281553
Buffers: shared read=32579
-> Seq Scan on TABLE1_monthly_2019_06_25 (cost=32731.14..42458.60 rows=177116 width=16) (actual time=923.367..1141.672 rows=311351 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Rows Removed by Filter: 42880
Buffers: shared read=4414
-> Seq Scan on TABLE1_monthly_2019_07_25 (cost=32731.14..32748.04 rows=77 width=16) (actual time=0.008..0.008 rows=0 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
-> Seq Scan on TABLE1_monthly_2019_08_24 (cost=32731.14..32748.04 rows=77 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
-> Seq Scan on TABLE1_monthly_2019_09_23 (cost=32731.14..32748.04 rows=77 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
-> Seq Scan on TABLE1_monthly_2019_10_23 (cost=32731.14..32748.04 rows=77 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((event_timestamp > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (NOT (hashed SubPlan 1)))
Planning Time: 244.669 ms
Execution Time: 15959.111 ms
(69 rows)
A query that joins two large partitioned tables to produce 10 million rows is going to consume resources, there is no way around that.
You can trade memory consumption for speed by reducing work_mem: smaller vakues will make your queries slower, but consume less memory.
I'd say that the best thing would be to leave work_mem high but reduce max_connections so that you don't run out of memory so fast. Also, putting more RAM into the machine is one of the cheapest hardware tuning techniques.
You can improve the query slighty:
Remove the DISTINCT, which is useless, consumes CPU resources and throws your estimates off.
ANALYZE table2 so that you get better estimates.
About partitioning: if these queries scan all partitions, the query will be slower with partitioned tables.
Whether partitioning is a good idea for you or not depends on the question if you have other queries that benefit from partitioning:
First and foremost, mass deletion, which is painless by dropping partitions.
Sequential scans where the partitioning key is part of the scan filter.
Contrary to popular belief, partitioning is not something you always benefit from if you have large tables: many queries become slower by partitioning.
The locks are your least worry: just increase max_locks_per_transaction.