Last active
May 20, 2016 03:53
-
-
Save marcocitus/882eb9eda170239bcee9b0461f76ce91 to your computer and use it in GitHub Desktop.
Citus EXPLAIN example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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