explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VUI

Settings
# exclusive inclusive rows x rows loops node
1. 1.404 295.749 ↓ 6.0 683 1

Sort (cost=8,642.23..8,642.51 rows=113 width=188) (actual time=295.672..295.749 rows=683 loops=1)

  • Sort Key: preselect_authors.lastname, preselect_authors.firstname
  • Sort Method: quicksort Memory: 206kB
2.          

CTE preselect_authors

3. 16.338 16.338 ↓ 170.8 683 1

Seq Scan on authors (cost=0.00..2,135.89 rows=4 width=32) (actual time=0.246..16.338 rows=683 loops=1)

  • Filter: ((id_unit > '-1'::integer) AND (unaccent(lower("left"((lastname)::text, 1))) = unaccent('ś'::text)) AND (id_department = ANY ('{1,2,3,4,5,6,7,8,9,31,32,33,13}'::integer[])))
  • Rows Removed by Filter: 38272
4.          

CTE preselect_publications

5. 4.548 288.478 ↓ 6.0 683 1

GroupAggregate (cost=6,497.09..6,499.35 rows=113 width=20) (actual time=281.577..288.478 rows=683 loops=1)

  • Group Key: autpub.id_author
6. 10.510 283.930 ↓ 238.9 27,000 1

Sort (cost=6,497.09..6,497.37 rows=113 width=9) (actual time=281.566..283.930 rows=27,000 loops=1)

  • Sort Key: autpub.id_author
  • Sort Method: quicksort Memory: 2034kB
7. 10.692 273.420 ↓ 238.9 27,000 1

Nested Loop (cost=0.55..6,493.23 rows=113 width=9) (actual time=17.860..273.420 rows=27,000 loops=1)

8. 43.180 100.728 ↓ 238.9 27,000 1

Hash Semi Join (cost=0.13..6,411.76 rows=113 width=9) (actual time=17.809..100.728 rows=27,000 loops=1)

  • Hash Cond: (autpub.id_author = preselect_authors_1.id_author)
9. 40.619 40.619 ↑ 1.0 355,515 1

Seq Scan on autpub (cost=0.00..5,477.15 rows=355,515 width=9) (actual time=0.019..40.619 rows=355,515 loops=1)

10. 0.164 16.929 ↓ 170.8 683 1

Hash (cost=0.08..0.08 rows=4 width=4) (actual time=16.929..16.929 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
11. 16.765 16.765 ↓ 170.8 683 1

CTE Scan on preselect_authors preselect_authors_1 (cost=0.00..0.08 rows=4 width=4) (actual time=0.250..16.765 rows=683 loops=1)

12. 162.000 162.000 ↑ 1.0 1 27,000

Index Scan using publications_pkey on publications (cost=0.42..0.71 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=27,000)

  • Index Cond: (id_publication = autpub.id_publication)
  • Filter: ((publish_year >= 1900) AND (publish_year <= 2066))
13. 5.342 294.345 ↓ 6.0 683 1

Hash Left Join (cost=0.13..3.14 rows=113 width=188) (actual time=281.958..294.345 rows=683 loops=1)

  • Hash Cond: (preselect_publications.id_author = preselect_authors.id_author)
14. 288.690 288.690 ↓ 6.0 683 1

CTE Scan on preselect_publications (cost=0.00..2.26 rows=113 width=20) (actual time=281.580..288.690 rows=683 loops=1)

15. 0.162 0.313 ↓ 170.8 683 1

Hash (cost=0.08..0.08 rows=4 width=212) (actual time=0.313..0.313 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
16. 0.151 0.151 ↓ 170.8 683 1

CTE Scan on preselect_authors (cost=0.00..0.08 rows=4 width=212) (actual time=0.002..0.151 rows=683 loops=1)

Planning time : 2.451 ms
Execution time : 296.193 ms