explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yeAn

Settings
# exclusive inclusive rows x rows loops node
1. 0.141 96.484 ↑ 77.6 65 1

Sort (cost=12,634.44..12,647.06 rows=5,047 width=188) (actual time=96.480..96.484 rows=65 loops=1)

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

CTE preselect_authors

3. 16.049 16.049 ↑ 2.8 65 1

Seq Scan on authors (cost=0.00..1,941.12 rows=179 width=32) (actual time=0.387..16.049 rows=65 loops=1)

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

CTE preselect_publications

5. 0.805 95.671 ↑ 77.6 65 1

HashAggregate (cost=10,148.96..10,199.43 rows=5,047 width=20) (actual time=95.630..95.671 rows=65 loops=1)

  • Group Key: autpub.id_author
6. 0.434 94.866 ↑ 1.9 2,646 1

Nested Loop (cost=6.24..10,111.11 rows=5,047 width=9) (actual time=17.893..94.866 rows=2,646 loops=1)

7. 33.959 83.848 ↑ 1.9 2,646 1

Hash Semi Join (cost=5.82..6,472.34 rows=5,047 width=9) (actual time=17.853..83.848 rows=2,646 loops=1)

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

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

9. 0.022 16.122 ↑ 2.8 65 1

Hash (cost=3.58..3.58 rows=179 width=4) (actual time=16.122..16.122 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
10. 16.100 16.100 ↑ 2.8 65 1

CTE Scan on preselect_authors preselect_authors_1 (cost=0.00..3.58 rows=179 width=4) (actual time=0.390..16.100 rows=65 loops=1)

11. 10.584 10.584 ↑ 1.0 1 2,646

Index Scan using publications_pkey on publications (cost=0.42..0.71 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,646)

  • Index Cond: (id_publication = autpub.id_publication)
  • Filter: ((publish_year >= 1,900) AND (publish_year <= 2,066))
12. 0.606 96.343 ↑ 77.6 65 1

Hash Left Join (cost=5.82..183.47 rows=5,047 width=188) (actual time=95.750..96.343 rows=65 loops=1)

  • Hash Cond: (preselect_publications.id_author = preselect_authors.id_author)
13. 95.701 95.701 ↑ 77.6 65 1

CTE Scan on preselect_publications (cost=0.00..100.94 rows=5,047 width=20) (actual time=95.634..95.701 rows=65 loops=1)

14. 0.020 0.036 ↑ 2.8 65 1

Hash (cost=3.58..3.58 rows=179 width=212) (actual time=0.036..0.036 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
15. 0.016 0.016 ↑ 2.8 65 1

CTE Scan on preselect_authors (cost=0.00..3.58 rows=179 width=212) (actual time=0.002..0.016 rows=65 loops=1)

Planning time : 1.106 ms
Execution time : 96.706 ms