so targetnamecache is a component of the slowness here:
- there are 26K products
- and 37K sourcepackagenames
- but 700K bugs, so we're ilike searchings 15 times as much data.
lpmain_staging=> select count(*) from sourcepackagename where sourcepackagename.name ilike '%bluray%';
count
-------
1
(1 row)
Time: 37.813 ms
lpmain_staging=> select count(*) from bugtask where bugtask.targetnamecache ilike '%bluray%';
count
-------
0
(1 row)
Time: 1113.370 ms
lpmain_staging=> select count(*) from bugtask,sourcepackagename where bugtask.sourcepackagename=sourcepackagename.id and sourcepackagename.name ilike '%bluray%';
count
-------
0
(1 row)
so targetnamecache is a component of the slowness here:
- there are 26K products
- and 37K sourcepackagenames
- but 700K bugs, so we're ilike searchings 15 times as much data.
lpmain_staging=> select count(*) from sourcepackagename where sourcepackagena me.name ilike '%bluray%';
count
-------
1
(1 row)
Time: 37.813 ms
lpmain_staging=> select count(*) from bugtask where bugtask. targetnamecache ilike '%bluray%';
count
-------
0
(1 row)
Time: 1113.370 ms
lpmain_staging=> select count(*) from bugtask, sourcepackagena me where bugtask. sourcepackagena me=sourcepackag ename.id and sourcepackagena me.name ilike '%bluray%';
count
-------
0
(1 row)
Time: 74.883 ms