If you are using partitioned tables, make sure you have enabled partition pruning:
SET enable_partition_pruning to on;
The explain statement is simple enough:
explain select * from documents where dokumenttype='SUBPOENA'; QUERY PLAN --------------------------------------------------------------------- Append (cost=0.00..1.02 rows=1 width=774) -> Seq Scan on P_SUBPOENA (cost=0.00..1.01 rows=1 width=774) Filter: ((documenttype)::text = 'SUBPOENA'::text) (3 rows)Since this is the LIST-partitioned table outlined in this post, I know the optimizer picked the correct partition for my predicate.
For the LIST-range subpartitioned table outlined in this post, I get the following query plan:
explain select * from orders where country_code='se' and order_total between 4000 and 4999; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..28.21 rows=1 width=50) -> Seq Scan on large_orders_se (cost=0.00..28.20 rows=1 width=50) Filter: ((order_total >= '4000'::numeric) AND (order_total <= '4999'::numeric) AND ((country_code)::text = 'se'::text)) (3 rows)
Regarding parallelism, the documentation tells you to watch for the terms Gather or Gather Merge Node in the query plan:
An example:
"Subquery Scan on andre (cost=1000.00..10024950.78 rows=294885 width=84)" " -> Gather (cost=1000.00..10013892.60 rows=294885 width=64)" " Workers Planned: 2" " -> ProjectSet (cost=0.00..9983404.10 rows=29488500 width=64)" " -> ProjectSet (cost=0.00..9830063.90 rows=12286900 width=128)"
No comments:
Post a Comment