Hi, Attached is a query and its corresponding plan, where sorting of the CTE acts seems to be the bottle neck. It is a real execution plan captured with the auto_explain module. The query is recursive. In each iteration CTE acts is sorted again, which is obviously quite expensive for about 24000 rows and the same number of iterations. So I tried to put the ordering over the keys (d_id, activation_count) into the CTE definition itself. This is honoured, when evaluating the CTE but not for the iteration, where the CTE acts is still sorted again. I cannot see a reason for this. A simple CTE scan with filter condition should be enough. Removing the order by from the definition of the CTE has absolutely no impact on the performance, which is quite obvious regarding the number of iterations. Further it has no impact on the query plan at all. It only removes the sort node from the CTE acts node. Do I miss something which would make the plan incorrect or is the planner just not intelligent enough to recognize that a table is sorted by the desired keys? I hope the attachments prevent outlook from destroying any text formatting. Thanks in advance -- Regards, Robert
CTE Scan on he_per_dispenser (cost=75008.64..75720.96 rows=35616 width=16) (actual time=141.806..205579.272 rows=53152 loops=1) Output: he_per_dispenser.he_id, he_per_dispenser.activation_id CTE acts -> Sort (cost=8298.16..8431.04 rows=53152 width=24) (actual time=113.128..117.709 rows=53152 loops=1) Output: a.id, e.dispenser_id, a.activation_count, e."timestamp" Sort Key: e.dispenser_id, a.activation_count Sort Method: quicksort Memory: 5689kB -> Hash Join (cost=1531.92..4126.30 rows=53152 width=24) (actual time=25.653..66.205 rows=53152 loops=1) Output: a.id, e.dispenser_id, a.activation_count, e."timestamp" Hash Cond: (e.id = a.id) -> Seq Scan on events e (cost=0.00..1280.93 rows=78193 width=20) (actual time=0.006..8.615 rows=78193 loops=1) Output: e.id, e."timestamp", e.dispenser_id, e.relocation_id, e.synthetic -> Hash (cost=867.52..867.52 rows=53152 width=12) (actual time=18.737..18.737 rows=53152 loops=1) Output: a.id, a.activation_count -> Seq Scan on activations a (cost=0.00..867.52 rows=53152 width=12) (actual time=0.005..8.938 rows=53152 loops=1) Output: a.id, a.activation_count CTE he_per_dispenser -> Recursive Union (cost=1369.30..66577.60 rows=35616 width=40) (actual time=141.803..205530.157 rows=53152 loops=1) -> Hash Join (cost=1369.30..5359.69 rows=266 width=40) (actual time=141.803..155.424 rows=11 loops=1) Output: a.activation_id, acts.dispenser_id, (min(acts.activation_count)), a."timestamp", CASE WHEN (a."timestamp" <= (p."timestamp" + $1)) THEN p.last_hygiene_event ELSE a.activation_id END Hash Cond: ((a.dispenser_id = acts.dispenser_id) AND (a.activation_count = (min(acts.activation_count)))) -> CTE Scan on acts a (cost=0.00..1063.04 rows=53152 width=24) (actual time=113.130..118.725 rows=53152 loops=1) Output: a.activation_id, a.dispenser_id, a.activation_count, a."timestamp" -> Hash (cost=1366.30..1366.30 rows=200 width=24) (actual time=28.644..28.644 rows=11 loops=1) Output: acts.dispenser_id, (min(acts.activation_count)), p."timestamp", p.last_hygiene_event -> Hash Left Join (cost=1359.05..1366.30 rows=200 width=24) (actual time=28.634..28.639 rows=11 loops=1) Output: acts.dispenser_id, (min(acts.activation_count)), p."timestamp", p.last_hygiene_event Hash Cond: (acts.dispenser_id = p.dispenser_id) -> HashAggregate (cost=1328.80..1331.30 rows=200 width=8) (actual time=28.617..28.620 rows=11 loops=1) Output: acts.dispenser_id, min(acts.activation_count) -> CTE Scan on acts (cost=0.00..1063.04 rows=53152 width=8) (actual time=0.001..15.560 rows=53152 loops=1) Output: acts.activation_id, acts.dispenser_id, acts.activation_count, acts."timestamp" -> Hash (cost=19.00..19.00 rows=900 width=20) (actual time=0.002..0.002 rows=0 loops=1) Output: p."timestamp", p.last_hygiene_event, p.dispenser_id -> Seq Scan on processed_activations_cache p (cost=0.00..19.00 rows=900 width=20) (actual time=0.002..0.002 rows=0 loops=1) Output: p."timestamp", p.last_hygiene_event, p.dispenser_id -> Merge Join (cost=5439.41..6050.56 rows=3535 width=40) (actual time=3.172..8.274 rows=2 loops=24813) Output: a.activation_id, a.dispenser_id, a.activation_count, a."timestamp", CASE WHEN ((a."timestamp" - s."timestamp") <= $1) THEN s.he_id ELSE a.activation_id END Merge Cond: ((s.dispenser_id = a.dispenser_id) AND (((s.activation_count + 1)) = a.activation_count)) -> Sort (cost=204.52..211.17 rows=2660 width=24) (actual time=0.005..0.006 rows=2 loops=24813) Output: s."timestamp", s.he_id, s.dispenser_id, s.activation_count Sort Key: s.dispenser_id, ((s.activation_count + 1)) Sort Method: quicksort Memory: 25kB -> WorkTable Scan on he_per_dispenser s (cost=0.00..53.20 rows=2660 width=24) (actual time=0.001..0.001 rows=2 loops=24813) Output: s."timestamp", s.he_id, s.dispenser_id, s.activation_count -> Sort (cost=5234.90..5367.78 rows=53152 width=24) (actual time=0.001..2.540 rows=40748 loops=24813) Output: a.activation_id, a.dispenser_id, a.activation_count, a."timestamp" Sort Key: a.dispenser_id, a.activation_count Sort Method: quicksort Memory: 5689kB -> CTE Scan on acts a (cost=0.00..1063.04 rows=53152 width=24) (actual time=0.000..5.727 rows=53152 loops=1) Output: a.activation_id, a.dispenser_id, a.activation_count, a."timestamp"
-- $1 = INTERVAL'1.5s' in the execution plan INSERT INTO transformation.high_level_events( id, activation_id) WITH RECURSIVE acts AS ( /* This CTE should prevent duplicate physical tablescans. * An order by as the plan suggests, seems worthless. The * executed plan will will sort this CTE again in each iteration * of he_per_day. */ SELECT a.id AS activation_id, e.day_id, a.activation_count, e.timestamp FROM transformation.activations a JOIN transformation.events e USING(id) ORDER BY day_id, activatoin_count), he_per_day AS ( SELECT a.activation_id, min_acts.day_id, min_acts.activation_count, a.timestamp, CASE WHEN a.timestamp <= p.timestamp + $1 THEN p.last_high_level_event ELSE a.activation_id END AS he_id -- identifies a specific high level event FROM (SELECT day_id, MIN(activation_count) AS activation_count FROM acts GROUP BY day_id) min_acts JOIN acts a USING (day_id, activation_count) -- check whether it belongs to the last high_level event processed LEFT OUTER JOIN transformation.processed_activations_cache p USING (day_id) UNION ALL SELECT a.activation_id, a.day_id, a.activation_count, a.timestamp, CASE WHEN a.timestamp - s.timestamp <= $1 THEN s.he_id ELSE a.activation_id END AS he_id FROM acts a JOIN he_per_day s ON a.day_id = s.day_id AND a.activation_count = s.activation_count + 1) SELECT he_id AS id, activation_id FROM he_per_day;
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
