Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Citus EXPLAIN example
postgres=# EXPLAIN ANALYZE SELECT p.product_id, p.name, p.price, to_json(array_agg(to_json(o)))
FROM product p LEFT JOIN offer o USING (product_id)
WHERE product_text_search(p.name, p.description) @@ plainto_tsquery('copper oven')
AND (p.price < 70 OR o.price < 70)
AND (p.attributes @> '{"food":"waste"}' OR p.attributes @> '{"food":"air"}')
GROUP BY p.product_id, p.name, p.description, p.price
ORDER BY ts_rank(product_text_search(p.name, p.description),
plainto_tsquery('copper oven')) DESC
LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Distributed Query into pg_merge_job_0164
Task Count: 16
Tasks Shown: One out of 16
-> Task
Node: host=ip-10-192-0-115.ec2.internal port=5432 dbname=postgres
-> Limit (cost=68.96..68.97 rows=4 width=396) (actual time=4.868..4.868 rows=0 loops=1)
-> Sort (cost=68.96..68.97 rows=4 width=396) (actual time=4.865..4.865 rows=0 loops=1)
Sort Key: (ts_rank(product_text_search(p.name, p.description), plainto_tsquery('copper oven'::text))) DESC, p.product_id, p.name, p.description, p.price
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=66.86..68.92 rows=4 width=396) (actual time=4.791..4.791 rows=0 loops=1)
Group Key: p.product_id, p.name, p.description, p.price
-> Nested Loop Left Join (cost=51.33..66.80 rows=4 width=396) (actual time=4.789..4.789 rows=0 loops=1)
Filter: ((p.price < '70'::numeric) OR (o.price < '70'::numeric))
-> Bitmap Heap Scan on product_102104 p (cost=50.91..56.98 rows=4 width=355) (actual time=4.787..4.787 rows=0 loops=1)
Recheck Cond: (((attributes @> '{"food": "waste"}'::jsonb) OR (attributes @> '{"food": "air"}'::jsonb)) AND (product_text_search(name, desc
ription) @@ plainto_tsquery('copper oven'::text)))
-> BitmapAnd (cost=50.91..50.91 rows=4 width=0) (actual time=4.783..4.783 rows=0 loops=1)
-> BitmapOr (cost=21.38..21.38 rows=1250 width=0) (actual time=0.081..0.081 rows=0 loops=1)
-> Bitmap Index Scan on attributes_idx_102104 (cost=0.00..10.69 rows=625 width=0) (actual time=0.052..0.052 rows=22 loops=1)
Index Cond: (attributes @> '{"food": "waste"}'::jsonb)
-> Bitmap Index Scan on attributes_idx_102104 (cost=0.00..10.69 rows=625 width=0) (actual time=0.026..0.026 rows=17 loops=1)
Index Cond: (attributes @> '{"food": "air"}'::jsonb)
-> Bitmap Index Scan on text_idx_102104 (cost=0.00..29.28 rows=2138 width=0) (actual time=4.672..4.672 rows=2087 loops=1)
Index Cond: (product_text_search(name, description) @@ plainto_tsquery('copper oven'::text))
-> Index Scan using offer_pkey_102120 on offer_102120 o (cost=0.42..2.44 rows=1 width=49) (never executed)
Index Cond: (p.product_id = product_id)
Planning time: 3.107 ms
Execution time: 5.066 ms
Master Query
-> Limit (cost=0.29..0.32 rows=10 width=0) (actual time=0.033..0.037 rows=2 loops=1)
-> Sort (cost=0.29..0.32 rows=10 width=0) (actual time=0.032..0.033 rows=2 loops=1)
Sort Key: intermediate_column_164_4 DESC
Sort Method: quicksort Memory: 26kB
-> HashAggregate (cost=0.00..0.12 rows=10 width=0) (actual time=0.021..0.023 rows=2 loops=1)
Group Key: intermediate_column_164_0, intermediate_column_164_1, intermediate_column_164_5, intermediate_column_164_2
-> Seq Scan on pg_merge_job_0164 (cost=0.00..0.00 rows=0 width=0) (actual time=0.003..0.005 rows=2 loops=1)
Planning time: 8.434 ms
Execution time: 0.098 ms
(38 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment