Query plan from staging:
Limit (cost=27.22..5475.19 rows=76 width=8) (actual time=315.823..3760.106 rows=76 loops=1) Buffers: shared hit=804261 read=346635 InitPlan 1 (returns $0) -> Aggregate (cost=4.29..4.30 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.43..4.29 rows=2 width=4) (actual time=0.009..0.010 rows=2 loops=1) Index Cond: (person = 1750507) Buffers: shared hit=5 InitPlan 2 (returns $2) -> Aggregate (cost=22.19..22.20 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1) Buffers: shared hit=9 read=2 -> Nested Loop (cost=0.85..22.18 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1) Buffers: shared hit=9 read=2 -> Index Scan using teamparticipation_person_idx on teamparticipation teamparticipation_1 (cost=0.43..4.29 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1) Index Cond: (person = 1750507) Buffers: shared hit=5 -> Index Only Scan using accesspolicygrant__grantee__policy__key on accesspolicygrant (cost=0.42..8.91 rows=4 width=8) (actual time=0.011..0.011 rows=0 loops=2) Index Cond: (grantee = teamparticipation_1.team) Heap Fetches: 0 Buffers: shared hit=4 read=2 -> Nested Loop Left Join (cost=0.72..489672.33 rows=6831 width=8) (actual time=315.822..3760.061 rows=76 loops=1) Filter: ((bugtaskflat.product IS NULL) OR product.active) Rows Removed by Filter: 1 Buffers: shared hit=804261 read=346635 -> Index Scan Backward using bugtaskflat__importance__bugtask__idx on bugtaskflat (cost=0.43..479039.06 rows=7735 width=12) (actual time=191.217..3759.210 rows=77 loops=1) Filter: ((duplicateof IS NULL) AND ((fti)::tsvector @@ '''promot'''::tsquery) AND ((information_type = ANY ('{1,2}'::integer[])) OR COALESCE((access_grants && $0), false) OR COALESCE((access_policies && $2), false)) AND (status = ANY ('{10,13,14,20,21,22,25}'::integer[]))) Rows Removed by Filter: 762329 Buffers: shared hit=804178 read=346592 -> Index Scan using product_pkey on product (cost=0.29..1.36 rows=1 width=5) (actual time=0.007..0.008 rows=1 loops=77) Index Cond: (bugtaskflat.product = id) Filter: active Rows Removed by Filter: 0 Buffers: shared hit=83 read=43 Total runtime: 3760.232 ms (34 rows)
Other queries are indeed very much faster (sub-100ms), so there must be something wrong with BugTaskFlat.fti.
Query plan from staging:
Limit (cost=27. 22..5475. 19 rows=76 width=8) (actual time=315. 823..3760. 106 rows=76 loops=1) on_person_ idx on teamparticipation (cost=0.43..4.29 rows=2 width=4) (actual time=0.009..0.010 rows=2 loops=1)
Index Cond: (person = 1750507)
Buffers: shared hit=5
Buffers: shared hit=9 read=2 on_person_ idx on teamparticipation teamparticipation_1 (cost=0.43..4.29 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)
Index Cond: (person = 1750507)
Buffers: shared hit=5 nt__grantee_ _policy_ _key on accesspolicygrant (cost=0.42..8.91 rows=4 width=8) (actual time=0.011..0.011 rows=0 loops=2)
Index Cond: (grantee = teamparticipati on_1.team)
Heap Fetches: 0
Buffers: shared hit=4 read=2 72..489672. 33 rows=6831 width=8) (actual time=315. 822..3760. 061 rows=76 loops=1) product IS NULL) OR product.active) _importance_ _bugtask_ _idx on bugtaskflat (cost=0. 43..479039. 06 rows=7735 width=12) (actual time=191. 217..3759. 210 rows=77 loops=1)
Filter: ((duplicateof IS NULL) AND ((fti)::tsvector @@ '''promot' ''::tsquery) AND ((information_type = ANY ('{1,2} '::integer[ ])) OR COALESCE( (access_ grants && $0), false) OR COALESCE( (access_ policies && $2), false)) AND (status = ANY ('{10,13, 14,20,21, 22,25}' ::integer[ ])))
Buffers: shared hit=804178 read=346592
Index Cond: (bugtaskflat. product = id)
Filter: active
Buffers: shared hit=83 read=43
Buffers: shared hit=804261 read=346635
InitPlan 1 (returns $0)
-> Aggregate (cost=4.29..4.30 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)
Buffers: shared hit=5
-> Index Scan using teamparticipati
InitPlan 2 (returns $2)
-> Aggregate (cost=22.19..22.20 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)
Buffers: shared hit=9 read=2
-> Nested Loop (cost=0.85..22.18 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
-> Index Scan using teamparticipati
-> Index Only Scan using accesspolicygra
-> Nested Loop Left Join (cost=0.
Filter: ((bugtaskflat.
Rows Removed by Filter: 1
Buffers: shared hit=804261 read=346635
-> Index Scan Backward using bugtaskflat_
Rows Removed by Filter: 762329
-> Index Scan using product_pkey on product (cost=0.29..1.36 rows=1 width=5) (actual time=0.007..0.008 rows=1 loops=77)
Rows Removed by Filter: 0
Total runtime: 3760.232 ms
(34 rows)
Other queries are indeed very much faster (sub-100ms), so there must be something wrong with BugTaskFlat.fti.