explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vQmU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 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
  • 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_authors

5. 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
6.          

CTE preselect_publications

7. 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
8. 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)

9. 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)
10. 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)

11. 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: 1024 Batches: 1 Memory Usage: 11kB
12. 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)

13. 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 >= 1900) AND (publish_year <= 2066))
14.          

CTE preselect_publications

15. 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
16. 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)

17. 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)
18. 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)

19. 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: 1024 Batches: 1 Memory Usage: 11kB
20. 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)

21. 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 >= 1900) AND (publish_year <= 2066))
22. 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)
23. 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)

24. 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
  • Rows Removed by Filter: 38890
25. 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)

26. 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)
27. 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)

28. 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: 1024 Batches: 1 Memory Usage: 13kB
29. 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