Hi, On Tue, Jul 23, 2019 at 3:29 PM Alexander Voytsekhovskyy <young.in...@gmail.com> wrote: > > 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?
Which version of PostgreSQL do you have? Which OS does it running on? Thank you.
