tonacatecuhtli-prod=> explain with bundle_ids as ( SELECT osub.id as order_id ,osub.name AS shopify_order_id ,RIGHT(unnested_tags, -10) AS bundle_timestamp ,CONCAT(RIGHT(unnested_tags, -10), customer_id) as bundle_id FROM ( SELECT o.id ,o.name ,o.customer_id ,unnest(o.tags) AS unnested_tags FROM shopify.order o ) osub WHERE unnested_tags LIKE '%bundle_id%' ) ,oli_count as ( select order_id ,count(oli_id) as order_line_count from ( select distinct o.id as order_id ,oli.id as oli_id from shopify.order o left join shopify.order_line_item oli on o.id = oli.order_id ) as temp group by order_id ) select date_trunc('day', o.created_at at time zone 'america/chicago') as created_at_cst_date ,o.created_at at time zone 'america/chicago' as created_at_cst ,ot.kind as ot_kind ,b.bundle_id ,o.id as order_id ,o.name as shopify_order_id ,o.source_name ,oli.id as oli_id ,oli.sku ,oli.properties ->> '_daily_deal' as daily_deal ,oli.properties ->> '_on_sale' as on_sale ,o.total_line_items_price as o_subtotal ,o.total_shipping_price_set_presentment_money_amount as o_shipping_amount ,o.total_discounts as o_total_discounts ,o.total_tax as o_total_tax ,oc.order_line_count as total_order_line_count ,oli.price as oli_price ,oli.quantity as oli_quantity ,sum(olitl.price) as oli_tax_amount from shopify.order_transaction ot left join shopify.order o on ot.order_id = o.id left join shopify.order_line_item oli on ot.order_id = oli.order_id left join shopify.order_line_item_tax_line olitl on oli.id = olitl.order_line_item_id left join bundle_ids b on o.id = b.order_id left join oli_count oc on o.id = oc.order_id where date_trunc('day', o.created_at at time zone 'america/chicago') = '2023-11-30' group by date_trunc('day', o.created_at at time zone 'america/chicago') ,o.created_at at time zone 'america/chicago' ,ot.kind ,b.bundle_id ,o.id ,o.name ,o.source_name ,oli.id ,oli.sku ,oc.order_line_count order by o.created_at at time zone 'america/chicago' ,o.id ,oli.id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=11430884.14..11488360.76 rows=364594 width=231) Group Key: (timezone('america/chicago'::text, o.created_at)), o.id, oli.id, (date_trunc('day'::text, timezone('america/chicago'::text, o.created_at))), ot.kind, (concat("right"(osub.unnested_tags, '-10'::integer), osub.customer_id)), oc.order_line_count -> Gather Merge (cost=11430884.14..11472409.78 rows=303828 width=290) Workers Planned: 2 -> Partial GroupAggregate (cost=11429884.12..11436340.46 rows=151914 width=290) Group Key: (timezone('america/chicago'::text, o.created_at)), o.id, oli.id, (date_trunc('day'::text, timezone('america/chicago'::text, o.created_at))), ot.kind, (concat("right"(osub.unnested_tags, '-10'::integer), osub.customer_id)), oc.order_line_count -> Sort (cost=11429884.12..11430263.90 rows=151914 width=262) Sort Key: (timezone('america/chicago'::text, o.created_at)), o.id, oli.id, ot.kind, (concat("right"(osub.unnested_tags, '-10'::integer), osub.customer_id)), oc.order_line_count -> Hash Left Join (cost=10090177.04..11398116.22 rows=151914 width=262) Hash Cond: (o.id = oc.order_id) -> Parallel Hash Left Join (cost=4888639.22..6195040.27 rows=151914 width=246) Hash Cond: (o.id = osub.id) -> Parallel Hash Left Join (cost=2363975.56..3669806.78 rows=151914 width=214) Hash Cond: (oli.id = olitl.order_line_item_id) -> Nested Loop Left Join (cost=1633049.02..2869969.45 rows=76311 width=210) -> Parallel Hash Join (cost=1633048.59..2678008.60 rows=10349 width=69) Hash Cond: (ot.order_id = o.id) -> Parallel Seq Scan on order_transaction ot (cost=0.00..1039526.62 rows=2069862 width=13) -> Parallel Hash (cost=1632889.36..1632889.36 rows=12738 width=56) -> Parallel Seq Scan on "order" o (cost=0.00..1632889.36 rows=12738 width=56) Filter: (date_trunc('day'::text, timezone('america/chicago'::text, created_at)) = '2023-11-30 00:00:00'::timestamp without time zone) -> Index Scan using order_line_item_order_id_idx on order_line_item oli (cost=0.43..18.16 rows=39 width=157) Index Cond: (order_id = ot.order_id) -> Parallel Hash (cost=508873.46..508873.46 rows=12774246 width=12) -> Parallel Seq Scan on order_line_item_tax_line olitl (cost=0.00..508873.46 rows=12774246 width=12) -> Parallel Hash (cost=2524587.24..2524587.24 rows=6114 width=40) -> Subquery Scan on osub (cost=0.00..2524587.24 rows=6114 width=40) Filter: (osub.unnested_tags ~~ '%bundle_id%'::text) -> ProjectSet (cost=0.00..1760270.42 rows=25476210 width=80) -> Parallel Seq Scan on "order" o_1 (cost=0.00..1613782.21 rows=2547621 width=89) -> Hash (cost=5201535.32..5201535.32 rows=200 width=16) -> Subquery Scan on oc (cost=4855018.27..5201535.32 rows=200 width=16) -> GroupAggregate (cost=4855018.27..5201533.32 rows=200 width=16) Group Key: o_2.id -> Unique (cost=4855018.27..4970522.62 rows=15400580 width=16) -> Sort (cost=4855018.27..4893519.72 rows=15400580 width=16) Sort Key: o_2.id, oli_1.id -> Merge Left Join (cost=7271.91..2490066.97 rows=15400580 width=16) Merge Cond: (o_2.id = oli_1.order_id) -> Index Only Scan using order_pkey on "order" o_2 (cost=0.43..1576161.96 rows=6114290 width=8) -> Index Only Scan using order_line_item_order_id_id_idx on order_line_item oli_1 (cost=0.56..706112.03 rows=15400580 width=16) (41 rows)