launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT dsp.id, dsps.name, dsps.binpkgnames, rank FROM DistributionSourcePackage dsp JOIN (SELECT DISTINCT ON (spn.id) spn.id, spn.name, dspc, binpkgnames, CASE WHEN spn.name = 'linux-image' THEN 100 WHEN dspc.binpkgnames ~ ('(^| )' || 'linux-image' || '( |$)') THEN 75 WHEN spn.name ~ ('(^|.*-)' || 'linux-image' || '(-|$)') THEN 50 WHEN dspc.binpkgnames ~ ('(^| |.*-)' || 'linux-image' || '(-| |$)') THEN 25 ELSE 1 END as rank FROM SourcePackageName spn LEFT JOIN DistributionSourcePackageCache dspc ON dspc.sourcepackagename = spn.id LEFT JOIN Archive a ON dspc.archive = a.id AND a.purpose IN (1, 4) WHERE dspc.fti @@ to_tsquery('default', 'linux-image') LIMIT 60) dsps ON dsp.sourcepackagename = dsps.id WHERE dsp.distribution = 1 ORDER BY RANK DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=155.40..155.42 rows=5 width=62) (actual time=146.955..146.960 rows=38 loops=1)
Sort Key: (CASE WHEN (spn.name = 'linux-image'::text) THEN 100 WHEN (dspc.binpkgnames ~ '(^| )linux-image( |$)'::text) THEN 75 WHEN (spn.name ~ '(^|.*-)linux-image(-|$)'::text) THEN 50 WHEN (dspc.binpkgnames ~ '(^| |.*-)linux-image(-| |$)'::text) THEN 25 ELSE 1 END)
Sort Method: quicksort Memory: 41kB
Buffers: shared hit=10969, temp read=974 written=1402
-> Nested Loop (cost=113.94..155.35 rows=5 width=62) (actual time=144.493..146.926 rows=38 loops=1)
Buffers: shared hit=10969, temp read=974 written=1402
-> Limit (cost=113.65..113.67 rows=5 width=1343) (actual time=144.472..146.664 rows=60 loops=1)
Buffers: shared hit=10811, temp read=974 written=1402
-> Unique (cost=113.65..113.67 rows=5 width=1343) (actual time=144.471..146.652 rows=60 loops=1)
Buffers: shared hit=10811, temp read=974 written=1402
-> Sort (cost=113.65..113.66 rows=5 width=1343) (actual time=144.468..146.502 rows=430 loops=1)
Sort Key: spn.id
Sort Method: external merge Disk: 11208kB
Buffers: shared hit=10811, temp read=974 written=1402
-> Nested Loop (cost=52.33..113.59 rows=5 width=1343) (actual time=9.127..122.484 rows=906 loops=1)
Buffers: shared hit=10811
-> Bitmap Heap Scan on distributionsourcepackagecache dspc (cost=52.04..71.93 rows=5 width=1333) (actual time=8.980..59.392 rows=906 loops=1)
Recheck Cond: ((fti)::tsvector @@ '''linux-imag'' & ''linux'' & ''imag'''::tsquery)
Buffers: shared hit=7807
-> Bitmap Index Scan on distributionsourcepackagecache__fti__idx (cost=0.00..52.04 rows=5 width=0) (actual time=8.619..8.619 rows=906 loops=1)
Index Cond: ((fti)::tsvector @@ '''linux-imag'' & ''linux'' & ''imag'''::tsquery)
Buffers: shared hit=36
-> Index Scan using sourcepackagename_pkey on sourcepackagename spn (cost=0.29..8.31 rows=1 width=18) (actual time=0.004..0.005 rows=1 loops=906)
Index Cond: (id = dspc.sourcepackagename)
Buffers: shared hit=2722
-> Index Scan using distributionpackage__sourcepackagename__distribution__key on distributionsourcepackage dsp (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=60)
Index Cond: ((sourcepackagename = spn.id) AND (distribution = 1))
Buffers: shared hit=158
Total runtime: 149.411 ms
(29 rows)