| 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