SQL: Calculating Number of Concurrent Calls

In “Calculating Concurrent Sessions, Part 1” and “Calculating Concurrent Sessions, Part 2,” I covered a task to calculate the maximum number of concurrent sessions for each application. I started the series by presenting a set-based solution (call it Original Set-Based Solution) that didn’t perform well because it had quadratic algorithmic complexity. I also presented a cursor-based solution (call it Cursor-Based Solution) and explained that the cursor alternative performed better because it had linear complexity. In the second part of the series I presented a new set-based solution (call it New Set-Based Solution 1) with linear complexity. This solution performed better than the cursor-based solution, but it involved using a temporary table, a couple of scans of the data, plus a seek operation in an index for each row from the table.

I thought that New Set-Based Solution 1 was the best available, but I was pleasantly surprised to learn about a fantastic set-based solution (call it New Set-Based Solution 2) that performs even better. Several readers sent me this solution. New Set-Based Solution 2 doesn’t involve the use of any temporary tables, requires only two scans of the data, and has linear complexity. In a benchmark test that I ran, it proved to be an order of magnitude faster compared with New Set-Based Solution 1. I’d like to thank Ben Flanaghan, Arnold Fribble, and R. Barry Young for coming up with the new solution.

Metrics Maven: Calculating a Moving Average in PostgreSQL

<span class="token keyword">SELECT</span> <span class="token number">ad</span><span class="token punctuation">.</span>date<span class="token punctuation">,</span>  
       AVG<span class="token punctuation">(</span><span class="token number">ad</span><span class="token punctuation">.</span>downloads<span class="token punctuation">)</span>
            <span class="token keyword">OVER</span><span class="token punctuation">(</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">ad</span><span class="token punctuation">.</span>date <span class="token keyword">ROWS</span> <span class="token operator">BETWEEN</span> <span class="token keyword">UNBOUNDED</span> <span class="token keyword">PRECEDING</span> <span class="token operator">AND</span> <span class="token keyword">CURRENT</span> <span class="token keyword">ROW</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> avg_downloads_ytd
<span class="token keyword">FROM</span> app_downloads_by_date <span class="token number">ad</span>  
<span class="token punctuation">;</span>

Indiscriminate use of CTEs considered harmful

However, there is one aspect of the current implementation of CTEs that should make you pause. Currently CTEs are in effect materialized before they can be used. That is, Postgres runs the query and stashes the data in a temporary store before it can be used in the larger query. There are a number of consequences of this.

..  After some analysis and testing, the simple act of inlining two CTEs in the query in question resulted in the query running in 4% of the time it had previously taken. Indiscriminate use of CTEs had made the performance of this query 25 times worse.

Creating Pivot Tables in PostgreSQL Using the Crosstab Function

SELECT *
FROM crosstab( 'select extract(month from period)::text, subject.name,
             trunc(avg(evaluation_result),2)
     from evaluation, subject 
     where evaluation.subject_id = subject.subject_id and student_id = 1
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);