0

In my database I have two tables, assessments, and responses, in a 1:N relationship.

I have a query that looks a little something like this

  WITH "cross_responses" AS (select * from crosstab(
       $$select
         assessments.id as id,
         i.name as name,
         r.value as response
       from assessments
         left join users u on u.id = assessments.user_id
         inner join responses r on r.assessment_id = assessments.id
         inner join items i on i.id = r.item_id
       where i.name = ANY ('{E-mail Address, First Name, Last Name}')
       order by 1,2$$,
       $$VALUES('E-mail Address'), ('First Name'), ('Last Name')$$
     ) as ct(id int, email text, first_name text, last_name text)
) SELECT  ...blah blah...  
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST, 
assessments.start_time DESC NULLS LAST LIMIT 1000 OFFSET 4000

So basically, I'm creating a CTE made up a crosstabbed results for key responses from the assessment, then joining it two the assessments table, and selecting to get a nice flat representation mixing meta-data from the assessment, with the answers to individuals questions.

As you can see at the bottom, this query gets run in batches of 1000 when we are streaming a CSV file of all the results over HTTP.

I believe this results in a potential performance issue, because for each batch, I'm assuming crosstab will run against the entire assessment and responses tables, not against just the batch of defined by the outer query. Running explain analyze seemed to confirm this for as the crosstab line in the explain output, says it runs over 5080 rows, which is all the assessments in my development database (production has a lot more)

What I'm asking 1) Is my understanding about how crosstab behaves correct? 2) Is there a way to make the query passed into cross tab "aware" of limitations or conditions applied to the outer query? 3) Is there another approach I should be taking entirely to performantly run this query in a batched fashion to stream the data out to CSV.

EDIT: Adding the results of explain analyze

 Limit  (cost=1813.08..1813.08 rows=1 width=124) (actual time=71.913..72.036 rows=1000 loops=1)
  CTE cross_responses
    ->  Function Scan on crosstab ct  (cost=0.00..10.00 rows=1000 width=100) (actual time=63.593..64.074 rows=5080 loops=1)
  ->  Sort  (cost=1800.58..1803.08 rows=1000 width=124) (actual time=71.568..71.817 rows=5000 loops=1)
        Sort Key: assessments.id, assessments.end_time, assessments.start_time
        Sort Method: quicksort  Memory: 861kB
        ->  Hash Join  (cost=1712.00..1750.75 rows=1000 width=124) (actual time=66.358..69.842 rows=5080 loops=1)
              Hash Cond: (cross_responses.id = assessments.id)
              ->  CTE Scan on cross_responses  (cost=0.00..20.00 rows=1000 width=100) (actual time=63.595..65.204 rows=5080 loops=1)
              ->  Hash  (cost=1645.89..1645.89 rows=5289 width=28) (actual time=2.751..2.751 rows=5289 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 289kB
                    ->  Seq Scan on assessments  (cost=0.00..1645.89 rows=5289 width=28) (actual time=0.008..1.879 rows=5289 loops=1)
Total runtime: 72.384 ms
  • 1) To know exactly what is happening in your query use EXPLAIN. 2) You could a variable in the WITH part as WITH should be an auxiliary thing for building huge queries. 3) You can "parse" the EXPLAIN output here explain.depesz.com and start from there. You need to see how much it costs to run your query before you can optimize it. – Solrac Feb 22 '16 at 17:35
  • I added the results of explain analyze, which is part of why I was pretty sure about the performance issue. I can't yet run it in production unfortunately because we don't have the tablefunc extension installed. This is a new feature in development, and I want to make sure there aren't any obvious problems before doing too much development work. As for the the variable in the WITH statement, I might accept that as an answer if you provide more details in an answer post. – AndrewSwerlick Feb 22 '16 at 17:50
  • Well, It looks like the best way to optimize the query is by taking CTE away from it and @Craig-Ringer explains it here. – Solrac Feb 22 '16 at 18:15
  • As for the WITH statement you can use something like this: WITH var1 as (SELECT 8765 num), table1 as (SELECT 8765 id, 'data' datos) SELECT id, datos FROM table1, var1 WHERE table1.id = var1.num you just need to adapt it to your needs... – Solrac Feb 22 '16 at 18:16
  • There a a number of ways to optimize. How many rows are going to be there in the productive DB? Please provide your version of Postgres, the complete query and table definitions for items and assessments. – Erwin Brandstetter Feb 23 '16 at 0:27
1

If all the export can happen through a single database connection, or even better, through a single transaction, it would be simpler and more efficient to just use a server-side CURSOR instead of a pagination based on successive queries with LIMIT/OFFSET.

For instance:

BEGIN;

DECLARE cursor_name FOR
WITH "cross_responses" AS (select * from crosstab(
       $$select...
        ...
) SELECT  ...blah blah...  
ORDER BY "assessments"."id" desc, assessments.end_time DESC NULLS LAST, 
assessments.start_time DESC NULLS LAST

Then call the SQL statement FETCH 1000, stream the results to the web client, until FETCH returns no row. Then close the transaction, and the cursor will disappear with it.

It's also possible to not use a transaction if the cursor is declared WITH HOLD.


If multiple passes on the query must be run by independant connections (such as if there are multiple invocations by a web server), another possibility is to not use the crosstab() function, and do the pivot operation in the main query itself. This is not difficult because there are only 3 static columns.

For instance:

 select
  assessments.id as id,
  max(case when i.name='Last Name' then r.value end) as "Last Name",
  max(case when i.name='First Name' then r.value end) as "First Name",
  max(case when i.name='E-mail Address' then r.value end) as "Email"
 from assessments
     left join users u on u.id = assessments.user_id
     inner join responses r on r.assessment_id = assessments.id
     inner join items i on i.id = r.item_id
 where i.name = ANY ('{E-mail Address, First Name, Last Name}')
 group by assessments.id
 order by 1 LIMIT 1000 OFFSET 4000;
  • Daniel, cursors do seem like the way to go, particularly since I envision having more than 3 pivoted columns as this feature evolves. I just have to see if I can find a way to make the ORM(Active Record) use them. There are some gems floating around out there that claim to do this. I'll give this question another day or so and then accept it. – AndrewSwerlick Feb 22 '16 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.