Changes between Version 3 and Version 4 of DBA_partitioning

Timestamp:

06/09/25 01:31:14 (5 months ago)

Author:

216049

Comment:

--

DBA_partitioning

@@ -280 +280 @@
 ||5||205||1005||AI generated solution||2026-12-05 14:15:00||null||null||true||false||203.0.113.42||Edge/130.0||
 
-
-== Тест
-
-Во табелата 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
-}}}
-}}}