Changes between Version 2 and Version 3 of DBA_partitioning

Timestamp:

06/08/25 00:18:31 (5 months ago)

Author:

216049

Comment:

--

DBA_partitioning

@@ -283 +283 @@
 == Тест
 
+Во табелата test_instance се додаваат 5.000.000 тест записи со вредност за scheduled_for во ранг од 2022 до 2025 година. Истите квериња се извршени на партиционирана табела и на непартиционирана табела.
+
+1. Прво квери:
+
+{{{#!div
+{{{#!sql
+explain (analyse, buffers)
+select * from test_instance where scheduled_for between '2024-01-01' and '2024-03-01';
+}}}
+}}}
+
+2. Второ квери
+
+{{{#!div
+{{{#!sql
+explain (analyse, buffers)
+select * from test_instance where scheduled_for between '2024-01-01' and '2024-03-01' and scheduled_until between '2024-01-01' and '2024-10-01';
+}}}
+}}}
+
+3. 
+
+{{{#!div
+{{{#!sql
+explain (analyse, buffers)
+select *
+from test_instance
+where scheduled_for between '2024-01-01' and '2025-01-01';
+}}}
+}}}
+
+=== Без партиционирање
+
+
+
+=== Со партиционирање
+
+{{{#!div 
+{{{#!sql 
+Seq Scan on test_instance_2023_2024 test_instance  (cost=0.00..35022.53 rows=168312 width=112) (actual time=0.075..313.242 rows=167605 loops=1)
+  Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2024-03-01 00:00:00'::timestamp without time zone))
+  Rows Removed by Filter: 855497
+  Buffers: shared hit=3186 read=16490
+Planning Time: 0.103 ms
+Execution Time: 533.276 ms
+}}}
+}}}
+
+
+{{{#!div 
+{{{#!sql 
+Gather  (cost=1000.00..31034.15 rows=18323 width=112) (actual time=9.510..73.809 rows=18628 loops=1)
+  Workers Planned: 2
+  Workers Launched: 2
+  Buffers: shared hit=3218 read=16458
+  ->  Parallel Seq Scan on test_instance_2023_2024 test_instance  (cost=0.00..28201.85 rows=7635 width=112) (actual time=7.168..45.342 rows=6209 loops=3)
+        Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2024-03-01 00:00:00'::timestamp without time zone) AND (scheduled_until >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_until <= '2024-10-01 00:00:00'::timestamp without time zone))
+        Rows Removed by Filter: 334825
+        Buffers: shared hit=3218 read=16458
+Planning Time: 0.111 ms
+Execution Time: 99.826 ms
+}}}
+}}}
+
+{{{#!div 
+{{{#!sql 
+Append  (cost=0.00..63966.17 rows=1024027 width=112) (actual time=0.042..4192.731 rows=1023818 loops=1)
+  Buffers: shared hit=5077 read=27983
+  ->  Seq Scan on test_instance_2023_2024 test_instance_1  (cost=0.00..35022.53 rows=766644 width=112) (actual time=0.039..1100.165 rows=766431 loops=1)
+        Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2025-01-01 00:00:00'::timestamp without time zone))
+        Rows Removed by Filter: 256671
+        Buffers: shared hit=3154 read=16522
+  ->  Seq Scan on test_instance_2024_2025 test_instance_2  (cost=0.00..23823.50 rows=257383 width=112) (actual time=0.019..430.753 rows=257387 loops=1)
+        Filter: ((scheduled_for >= '2024-01-01 00:00:00'::timestamp without time zone) AND (scheduled_for <= '2025-01-01 00:00:00'::timestamp without time zone))
+        Rows Removed by Filter: 438580
+        Buffers: shared hit=1923 read=11461
+Planning Time: 0.144 ms
+Execution Time: 5525.093 ms
+}}}
+}}}