This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time.

When should I use an array and when to use values instead?

Is there any way to reduce a sequential search?

EXPLAIN ANALYZE 
SELECT DISTINCT gu.user_id 
FROM eb.groupusers gu 
JOIN eb."group" g ON gu.group_id = g.id 
    AND lower(g.group_code::text) = lower('HLVL1GS'::text)
JOIN eb.userexams ue ON ue.user_id = gu.user_id 
JOIN eb.examresult er ON er.userexams_id = ue.id 
WHERE NOT (gu.user_id IN (SELECT DISTINCT ha.user_id 
                          FROM eb.hgsresults_archive ha)) 
ORDER BY gu.user_id

Unique (cost=928649.65..928658.50 rows=1769 width=4) (actual time=62327.900..62327.915 rows=39 loops=1)"

Sort (cost=928649.65..928654.08 rows=1769 width=4) (actual time=62327.899..62327.902 rows=108 loops=1)" Sort Key: gu.user_id" Sort Method: quicksort Memory: 21kB"

Hash Join (cost=23790.71..928554.23 rows=1769 width=4) (actual time=62123.575..62327.842 rows=108 loops=1)" Hash Cond: (ue.id = er.userexams_id)"

Hash Join (cost=14238.04..918083.16 rows=2374 width=12) (actual time=1968.672..62220.437 rows=1033 loops=1)" Hash Cond: (gu.user_id = ue.user_id)"

Nested Loop (cost=1425.18..903442.04 rows=567 width=4) (actual time=129.328..62065.633 rows=886 loops=1)"

Seq Scan on "group" g (cost=0.00..34.95 rows=5 width=8) (actual time=0.243..0.700 rows=1 loops=1)" Filter: (lower((group_code)::text) = 'hlvl1gs'::text)"

Index Scan using "PK_groupusers" on groupusers gu (cost=1425.18..180678.29 rows=250 width=8)

(actual time=129.078..62064.642 rows=886 loops=1)" Index Cond: (group_id = g.id)" Filter: (NOT (SubPlan 1))" SubPlan 1"

Materialize (cost=1425.18..2048.78 rows=32974 width=8) (actual time=0.003..1.344 rows=17128 loops=24017)" ->

HashAggregate (cost=1425.18..1754.92 rows=32974 width=8) (actual time=16.277..20.815 rows=32974 loops=1)" "

-> Seq Scan on hgsresults_archive ha (cost=0.00..1342.74 rows=32974 width=8) (actual time=0.013..9.586 rows=32974 loops=1)" "

Hash (cost=7741.83..7741.83 rows=291683 width=12) (actual time=115.483..115.483 rows=291683 loops=1)" Buckets: 4096 Batches: 16 Memory Usage: 741kB"

Seq Scan on userexams ue (cost=0.00..7741.83 rows=291683 width=12) (actual time=0.013..58.304 rows=291683 loops=1)"

Hash (cost=5985.63..5985.63 rows=217363 width=8) (actual time=85.998..85.998 rows=217363 loops=1)" Buckets: 4096 Batches: 8 Memory Usage: 855kB"

Seq Scan on examresult er (cost=0.00..5985.63 rows=217363 width=8) (actual time=0.006..46.276 rows=217363 loops=1)" "Total runtime: 62330.811 ms"

  • 1
    Please post the output as you get it, with all the indentations. Otherwise it is very hard to figure out the query plan structure. And how do you see the query is memory or CPU intensive (or not)? – dezso Jun 13 '16 at 11:26

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.