launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM (SELECT name FROM sourcepackagename WHERE name LIKE '%man%' AND id IN (SELECT sourcepackagename FROM distributionsourcepackagecache WHERE archive IN (SELECT id FROM archive WHERE purpose IN (1, 4)) OR archive IS NULL) ORDER BY name LIMIT 10) AS spn UNION (SELECT name FROM binarypackagename WHERE name LIKE '%man%' AND id IN (SELECT binarypackagename FROM distroseriespackagecache WHERE archive IN (SELECT id FROM archive WHERE purpose IN (1, 4))) ORDER BY name LIMIT 10) ORDER BY name LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=30430.90..30430.93 rows=10 width=22) (actual time=5.940..5.943 rows=10 loops=1)
Buffers: shared hit=4024 read=150
-> Sort (cost=30430.90..30430.95 rows=20 width=22) (actual time=5.938..5.939 rows=10 loops=1)
Sort Key: sourcepackagename.name
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=4024 read=150
-> HashAggregate (cost=30430.27..30430.47 rows=20 width=22) (actual time=5.915..5.918 rows=18 loops=1)
Buffers: shared hit=4024 read=150
-> Append (cost=140.28..30430.22 rows=20 width=22) (actual time=0.270..5.888 rows=20 loops=1)
Buffers: shared hit=4024 read=150
-> Limit (cost=140.28..3703.05 rows=10 width=15) (actual time=0.270..3.722 rows=10 loops=1)
Buffers: shared hit=3158
-> Nested Loop Semi Join (cost=140.28..122699.76 rows=344 width=15) (actual time=0.270..3.718 rows=10 loops=1)
Buffers: shared hit=3158
-> Index Scan using sourcepackagename_name_key on sourcepackagename (cost=0.42..4913.42 rows=837 width=19) (actual time=0.056..3.379 rows=31 loops=1)
Filter: (name ~~ '%man%'::text)
Rows Removed by Filter: 3445
Buffers: shared hit=3024
-> Index Only Scan using distributionsourcepackagecache__sourcepackagename__archive__idx on distributionsourcepackagecache (cost=139.86..142.50 rows=4 width=4) (actual time=0.009..0.009 rows=0 loops=31)
Index Cond: (sourcepackagename = sourcepackagename.id)
Filter: ((hashed SubPlan 1) OR (archive IS NULL))
Rows Removed by Filter: 1
Heap Fetches: 0
Buffers: shared hit=134
SubPlan 1
-> Index Scan using archive__distribution__purpose__distro_archives__key on archive archive_1 (cost=0.14..139.27 rows=69 width=4) (actual time=0.006..0.060 rows=38 loops=1)
Index Cond: (purpose = ANY ('{1,4}'::integer[]))
Buffers: shared hit=38
-> Limit (cost=1.27..26726.87 rows=10 width=30) (actual time=1.996..2.161 rows=10 loops=1)
Buffers: shared hit=866 read=150
-> Nested Loop Semi Join (cost=1.27..457009.07 rows=171 width=30) (actual time=1.996..2.158 rows=10 loops=1)
Buffers: shared hit=866 read=150
-> Index Scan using binarypackagename_name_key on binarypackagename (cost=0.42..36299.84 rows=4887 width=34) (actual time=0.148..1.916 rows=17 loops=1)
Filter: (name ~~ '%man%'::text)
Rows Removed by Filter: 1120
Buffers: shared hit=737 read=148
-> Nested Loop Semi Join (cost=0.84..86.08 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=17)
Buffers: shared hit=129 read=2
-> Index Only Scan using distroseriespackagecache__binarypackagename__archive__idx on distroseriespackagecache (cost=0.43..75.25 rows=23 width=8) (actual time=0.006..0.007 rows=1 loops=17)
Index Cond: (binarypackagename = binarypackagename.id)
Heap Fetches: 16
Buffers: shared hit=65 read=2
-> Index Scan using archive_pkey on archive (cost=0.42..0.47 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=16)
Index Cond: (id = distroseriespackagecache.archive)
Filter: (purpose = ANY ('{1,4}'::integer[]))
Rows Removed by Filter: 0
Buffers: shared hit=64
Total runtime: 6.054 ms
(48 rows)