I have quite complicated query:
SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM (
SELECT to_char(delivery_data.delivery_date, 'YYYY-MM') as axis_x1, clients.id_client as axis_y1, delivery_data.amount * production_price.price * groups.discount as delivery_price
FROM delivery_data
JOIN client_tt ON (client_tt.id_client_tt = delivery_data.id_client_tt)
JOIN clients ON (client_tt.id_client = clients.id_client)
JOIN production ON (production.id = delivery_data.id_product)
JOIN groups ON (groups.id = delivery_data.delivery_group_id AND client_tt.id_group = groups.id AND groups.id = clients.id_group)
LEFT JOIN production_price on (delivery_data.id_product = production_price.id_production AND groups.price_list_id = production_price.price_list_id AND delivery_data.delivery_date BETWEEN production_price.date_from AND production_price.date_to)
WHERE delivery_data.delivery_date between '2019-03-01' AND '2019-06-30'
AND delivery_data.delivery_group_id IN (...short list of values...)
AND delivery_data.id_product IN ()) AS tmpsource
WHERE TRUE
GROUP BY GROUPING SETS ((axis_x1, axis_y1), (axis_x1), (axis_y1), ())
It runs well, took 1s and returns 4000 rows.
You can see explain analyze verbose here:
https://explain.depesz.com/s/AEWj
The problem is, when i wrap it to
A)
INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
SELECT .... SAME QUERY
OR even
B)
WITH rows AS (
... SAME SELECT QUERY ...
)
INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
SELECT * FROM rows
The query time dramatically drops to 500+ seconds.
You can see explain analyze verbose here
https://explain.depesz.com/s/AEWj
As you can see, 100% of time goes to same SELECT query, there is no issues with INSERT-part
I have played a lot and it's reproducing all time.
So my question is, why wrapping SELECT query with INSERT FROM SELECT dramatically change query plan and make it 500x slower?
SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM (
SELECT to_char(delivery_data.delivery_date, 'YYYY-MM') as axis_x1, clients.id_client as axis_y1, delivery_data.amount * production_price.price * groups.discount as delivery_price
FROM delivery_data
JOIN client_tt ON (client_tt.id_client_tt = delivery_data.id_client_tt)
JOIN clients ON (client_tt.id_client = clients.id_client)
JOIN production ON (production.id = delivery_data.id_product)
JOIN groups ON (groups.id = delivery_data.delivery_group_id AND client_tt.id_group = groups.id AND groups.id = clients.id_group)
LEFT JOIN production_price on (delivery_data.id_product = production_price.id_production AND groups.price_list_id = production_price.price_list_id AND delivery_data.delivery_date BETWEEN production_price.date_from AND production_price.date_to)
WHERE delivery_data.delivery_date between '2019-03-01' AND '2019-06-30'
AND delivery_data.delivery_group_id IN (...short list of values...)
AND delivery_data.id_product IN ()) AS tmpsource
WHERE TRUE
GROUP BY GROUPING SETS ((axis_x1, axis_y1), (axis_x1), (axis_y1), ())
It runs well, took 1s and returns 4000 rows.
You can see explain analyze verbose here:
https://explain.depesz.com/s/AEWj
The problem is, when i wrap it to
A)
INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
SELECT .... SAME QUERY
OR even
B)
WITH rows AS (
... SAME SELECT QUERY ...
)
INSERT INTO norepl_1542_result (axis_x1, axis_y1, v_1)
SELECT * FROM rows
The query time dramatically drops to 500+ seconds.
You can see explain analyze verbose here
https://explain.depesz.com/s/AEWj
As you can see, 100% of time goes to same SELECT query, there is no issues with INSERT-part
I have played a lot and it's reproducing all time.
So my question is, why wrapping SELECT query with INSERT FROM SELECT dramatically change query plan and make it 500x slower?
