Skip to content

Instantly share code, notes, and snippets.

@mineo

mineo/foo.sql

Created January 4, 2018 21:03
Show Gist options
  • Save mineo/29dcf2229d1e21b736f4ef97705de4ea to your computer and use it in GitHub Desktop.
Save mineo/29dcf2229d1e21b736f4ef97705de4ea to your computer and use it in GitHub Desktop.
Download ZIP
Raw
foo.sql
explain analyze SELECT area.gid, url.gid, url.url
FROM l_area_url
JOIN link AS l
ON l_area_url.link=l.id
JOIN link_type AS lt
ON lt.id=l.link_type
JOIN area
ON entity0=area.id
JOIN url
ON l_area_url.entity1=url.id
JOIN (
SELECT distinct area FROM
(SELECT area
FROM place
UNION ALL
SELECT area
FROM label
UNION ALL
SELECT area
FROM artist
UNION ALL
SELECT begin_area
FROM artist
UNION ALL
SELECT end_area
FROM artist
UNION ALL
SELECT area
FROM country_area
JOIN release_country
ON release_country.country = country_area.area
UNION ALL
SELECT entity0
FROM l_area_recording
UNION ALL
SELECT entity0
FROM l_area_release
UNION ALL
SELECT entity0
FROM l_area_work) as bar
)
as foo
on foo.area = area.id
WHERE
lt.id IN (355, 358)
AND
l_area_url.edits_pending=0
AND
url.edits_pending=0 ;
Raw
plan.txt
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=174294.48..176211.52 rows=3 width=74) (actual time=1651.608..1931.753 rows=38990 loops=1) │
│ -> Hash Join (cost=174294.05..176207.99 rows=3 width=20) (actual time=1651.602..1853.753 rows=38990 loops=1) │
│ Hash Cond: (l.link_type = lt.id) │
│ -> Nested Loop (cost=174285.44..176196.87 rows=664 width=24) (actual time=1651.571..1843.762 rows=64196 loops=1) │
│ -> Nested Loop (cost=174285.02..175871.13 rows=664 width=24) (actual time=1651.566..1766.504 rows=64196 loops=1) │
│ -> Nested Loop (cost=174284.60..175737.18 rows=200 width=24) (actual time=1651.559..1696.328 rows=20460 loops=1) │
│ -> HashAggregate (cost=174284.18..174286.18 rows=200 width=4) (actual time=1651.547..1657.128 rows=20461 loops=1) │
│ Group Key: place.area │
│ -> Append (cost=0.29..160410.80 rows=5549353 width=4) (actual time=0.042..1143.858 rows=5549353 loops=1) │
│ -> Index Only Scan using place_idx_area on place (cost=0.29..706.57 rows=26819 width=4) (actual time=0.041..9.085 rows=26819 loops=1) │
│ Heap Fetches: 0 │
│ -> Seq Scan on label (cost=0.00..2963.69 rows=128369 width=4) (actual time=0.006..19.685 rows=128369 loops=1) │
│ -> Seq Scan on artist (cost=0.00..31099.80 rows=1248680 width=4) (actual time=0.002..150.501 rows=1248680 loops=1) │
│ -> Seq Scan on artist artist_1 (cost=0.00..31099.80 rows=1248680 width=4) (actual time=0.001..183.068 rows=1248680 loops=1) │
│ -> Seq Scan on artist artist_2 (cost=0.00..31099.80 rows=1248680 width=4) (actual time=0.001..183.016 rows=1248680 loops=1) │
│ -> Hash Join (cost=7.78..46539.89 rows=1597857 width=4) (actual time=0.049..331.838 rows=1597857 loops=1) │
│ Hash Cond: (release_country.country = country_area.area) │
│ -> Seq Scan on release_country (cost=0.00..24561.57 rows=1597857 width=4) (actual time=0.003..74.633 rows=1597857 loops=1) │
│ -> Hash (cost=4.57..4.57 rows=257 width=4) (actual time=0.034..0.034 rows=257 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 18kB │
│ -> Seq Scan on country_area (cost=0.00..4.57 rows=257 width=4) (actual time=0.002..0.015 rows=257 loops=1) │
│ -> Seq Scan on l_area_recording (cost=0.00..789.68 rows=43068 width=4) (actual time=0.002..3.775 rows=43068 loops=1) │
│ -> Seq Scan on l_area_release (cost=0.00..80.57 rows=4357 width=4) (actual time=0.002..0.427 rows=4357 loops=1) │
│ -> Seq Scan on l_area_work (cost=0.00..52.43 rows=2843 width=4) (actual time=0.002..0.230 rows=2843 loops=1) │
│ -> Index Scan using area_pkey on area (cost=0.42..7.23 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=20461) │
│ Index Cond: (id = place.area) │
│ -> Index Scan using l_area_url_idx_uniq on l_area_url (cost=0.42..0.64 rows=3 width=12) (actual time=0.002..0.003 rows=3 loops=20460) │
│ Index Cond: (entity0 = area.id) │
│ Filter: (edits_pending = 0) │
│ -> Index Scan using link_pkey on link l (cost=0.42..0.48 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=64196) │
│ Index Cond: (id = l_area_url.link) │
│ -> Hash (cost=8.58..8.58 rows=2 width=4) (actual time=0.023..0.023 rows=2 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 9kB │
│ -> Index Only Scan using link_type_pkey on link_type lt (cost=0.27..8.58 rows=2 width=4) (actual time=0.013..0.019 rows=2 loops=1) │
│ Index Cond: (id = ANY ('{355,358}'::integer[])) │
│ Heap Fetches: 0 │
│ -> Index Scan using url_pkey on url (cost=0.43..1.17 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=38990) │
│ Index Cond: (id = l_area_url.entity1) │
│ Filter: (edits_pending = 0) │
│ Planning time: 4.685 ms │
│ Execution time: 1933.373 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(41 Zeilen)
Zeit: 1939,365 ms (00:01,939)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment