prod 2021-02-09T00:39:12.043-05:00 09 Feb 2021 05:39:12:042 WARN PostgresClient [] EXPLAIN ANALYZE WITH headrecords AS ( SELECT jsonb, (lower(f_unaccent(jsonb->>'title')) ) AS data_column FROM _mod_inventory_storage.instance WHERE (get_tsvector(f_unaccent(instance.jsonb->>'contributors')) @@ tsquery_phrase(f_unaccent('Bach, Johann Sebastian')) AND id in (select t.id from (select id as id, jsonb_array_elements(instance.jsonb->'contributors') as c ) as t where (get_tsvector(f_unaccent(t.c->>'name')) @@ tsquery_phrase(f_unaccent('Bach, Johann Sebastian'))))) AND left(lower(f_unaccent(jsonb->>'title')) ,600) < ( SELECT left(lower(f_unaccent(jsonb->>'title')) ,600) FROM _mod_inventory_storage.instance ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) OFFSET 10000 LIMIT 1 ) ORDER BY left(lower(f_unaccent(jsonb->>'title')) ,600) LIMIT 100 OFFSET 0 ), allrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS data_column FROM _mod_inventory_storage.instance WHERE (get_tsvector(f_unaccent(instance.jsonb->>'contributors')) @@ tsquery_phrase(f_unaccent('Bach, Johann Sebastian')) AND id in (select t.id from (select id as id, jsonb_array_elements(instance.jsonb->'contributors') as c ) as t where (get_tsvector(f_unaccent(t.c->>'name')) @@ tsquery_phrase(f_unaccent('Bach, Johann Sebastian'))))) AND (SELECT COUNT(*) FROM headrecords) < 100 ), totalCount AS (SELECT _mod_inventory_storage.count_estimate(' SELECT lower(f_unaccent(jsonb->>''title'')) AS data_column FROM _mod_inventory_storage.instance WHERE get_tsvector(f_unaccent(instance.jsonb->>''contributors'')) @@ tsquery_phrase(f_unaccent(''Bach, Johann Sebastian'')) AND id in (select t.id from (select id as id, jsonb_array_elements(instance.jsonb->''contributors'') as c ) as t where (get_tsvector(f_unaccent(t.c->>''name'')) @@ tsquery_phrase(f_unaccent(''Bach, Johann Sebastian''))))') AS count) SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION (SELECT jsonb, data_column, (SELECT count FROM totalCount) FROM allrecords ORDER BY data_column LIMIT 100 OFFSET 0 ) ORDER BY data_column 2021-02-09T00:39:12.043-05:00 Sort (cost=9860.07..9860.08 rows=2 width=72) (actual time=14461.809..14461.813 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 Sort Key: headrecords.data_column 2021-02-09T00:39:12.043-05:00 Sort Method: quicksort Memory: 53kB 2021-02-09T00:39:12.043-05:00 CTE headrecords 2021-02-09T00:39:12.043-05:00 -> Limit (cost=8504.15..8504.67 rows=1 width=610) (actual time=6546.478..6547.025 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 InitPlan 2 (returns $3) 2021-02-09T00:39:12.043-05:00 -> Limit (cost=7148.25..7148.96 rows=1 width=32) (actual time=191.785..191.785 rows=1 loops=1) 2021-02-09T00:39:12.043-05:00 -> Index Scan using instance_title_idx on instance (cost=0.69..7489591.80 rows=10478528 width=32) (actual time=0.077..191.017 rows=10001 loops=1) 2021-02-09T00:39:12.043-05:00 -> Result (cost=1355.19..1355.71 rows=1 width=610) (actual time=6546.477..6547.021 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 -> Sort (cost=1355.19..1355.19 rows=1 width=578) (actual time=6546.275..6546.277 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 Sort Key: ("left"(lower(f_unaccent((instance_1.jsonb ->> 'title'::text))), 600)) 2021-02-09T00:39:12.043-05:00 Sort Method: quicksort Memory: 29kB 2021-02-09T00:39:12.043-05:00 -> Bitmap Heap Scan on instance instance_1 (cost=1324.00..1355.18 rows=1 width=578) (actual time=2043.230..6546.210 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 Recheck Cond: (to_tsvector('simple'::regconfig, translate(f_unaccent((jsonb ->> 'contributors'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 Rows Removed by Index Recheck: 26 2021-02-09T00:39:12.043-05:00 Filter: (("left"(lower(f_unaccent((jsonb ->> 'title'::text))), 600) < $3) AND (SubPlan 1)) 2021-02-09T00:39:12.043-05:00 Rows Removed by Filter: 16391 2021-02-09T00:39:12.043-05:00 Heap Blocks: exact=7044 2021-02-09T00:39:12.043-05:00 -> Bitmap Index Scan on instance_contributors_idx_ft (cost=0.00..1324.00 rows=1 width=0) (actual time=7.501..7.501 rows=16438 loops=1) 2021-02-09T00:39:12.043-05:00 Index Cond: (to_tsvector('simple'::regconfig, translate(f_unaccent((jsonb ->> 'contributors'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 SubPlan 1 2021-02-09T00:39:12.043-05:00 -> Subquery Scan on t (cost=0.00..52.27 rows=1 width=16) (actual time=0.080..0.080 rows=1 loops=21) 2021-02-09T00:39:12.043-05:00 Filter: (to_tsvector('simple'::regconfig, translate(f_unaccent((t.c ->> 'name'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 Rows Removed by Filter: 2 2021-02-09T00:39:12.043-05:00 -> ProjectSet (cost=0.00..0.52 rows=100 width=48) (actual time=0.032..0.033 rows=3 loops=21) 2021-02-09T00:39:12.043-05:00 -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=21) 2021-02-09T00:39:12.043-05:00 CTE allrecords 2021-02-09T00:39:12.043-05:00 -> Result (cost=1324.04..1354.95 rows=1 width=578) (actual time=8.790..7340.180 rows=16412 loops=1) 2021-02-09T00:39:12.043-05:00 One-Time Filter: ($8 < 100) 2021-02-09T00:39:12.043-05:00 InitPlan 5 (returns $8) 2021-02-09T00:39:12.043-05:00 -> Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1) 2021-02-09T00:39:12.043-05:00 -> CTE Scan on headrecords headrecords_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.003 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 -> Bitmap Heap Scan on instance instance_2 (cost=1324.04..1354.95 rows=1 width=578) (actual time=8.710..6961.916 rows=16412 loops=1) 2021-02-09T00:39:12.043-05:00 Recheck Cond: (to_tsvector('simple'::regconfig, translate(f_unaccent((jsonb ->> 'contributors'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 Rows Removed by Index Recheck: 26 2021-02-09T00:39:12.043-05:00 Filter: (SubPlan 4) 2021-02-09T00:39:12.043-05:00 Heap Blocks: exact=7044 2021-02-09T00:39:12.043-05:00 -> Bitmap Index Scan on instance_contributors_idx_ft (cost=0.00..1324.00 rows=1 width=0) (actual time=7.509..7.509 rows=16438 loops=1) 2021-02-09T00:39:12.043-05:00 Index Cond: (to_tsvector('simple'::regconfig, translate(f_unaccent((jsonb ->> 'contributors'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 SubPlan 4 2021-02-09T00:39:12.043-05:00 -> Subquery Scan on t_1 (cost=0.00..52.27 rows=1 width=16) (actual time=0.058..0.058 rows=1 loops=16412) 2021-02-09T00:39:12.043-05:00 Filter: (to_tsvector('simple'::regconfig, translate(f_unaccent((t_1.c ->> 'name'::text)), '&'::text, ','::text)) @@ '''bach'' <-> ''johann'' <-> ''sebastian'''::tsquery) 2021-02-09T00:39:12.043-05:00 Rows Removed by Filter: 2 2021-02-09T00:39:12.043-05:00 -> ProjectSet (cost=0.00..0.52 rows=100 width=48) (actual time=0.021..0.021 rows=3 loops=16412) 2021-02-09T00:39:12.043-05:00 -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=16412) 2021-02-09T00:39:12.043-05:00 CTE totalcount 2021-02-09T00:39:12.043-05:00 -> Result (cost=0.00..0.26 rows=1 width=8) (actual time=523.174..523.174 rows=1 loops=1) 2021-02-09T00:39:12.043-05:00 -> Unique (cost=0.16..0.18 rows=2 width=72) (actual time=14461.618..14461.644 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 -> Sort (cost=0.16..0.17 rows=2 width=72) (actual time=14461.618..14461.623 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 Sort Key: headrecords.jsonb, headrecords.data_column, ($12) 2021-02-09T00:39:12.043-05:00 Sort Method: quicksort Memory: 53kB 2021-02-09T00:39:12.043-05:00 -> Append (cost=0.06..0.15 rows=2 width=72) (actual time=14442.317..14442.345 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 -> Result (cost=0.06..0.08 rows=1 width=72) (actual time=6547.049..6547.049 rows=0 loops=1) 2021-02-09T00:39:12.043-05:00 One-Time Filter: ($13 >= 100) 2021-02-09T00:39:12.043-05:00 InitPlan 9 (returns $12) 2021-02-09T00:39:12.043-05:00 -> CTE Scan on totalcount totalcount_1 (cost=0.00..0.02 rows=1 width=8) (never executed) 2021-02-09T00:39:12.043-05:00 InitPlan 10 (returns $13) 2021-02-09T00:39:12.043-05:00 -> Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=6547.047..6547.047 rows=1 loops=1) 2021-02-09T00:39:12.043-05:00 -> CTE Scan on headrecords headrecords_2 (cost=0.00..0.02 rows=1 width=0) (actual time=6546.480..6547.041 rows=21 loops=1) 2021-02-09T00:39:12.043-05:00 -> CTE Scan on headrecords (cost=0.06..0.08 rows=1 width=72) (never executed) 2021-02-09T00:39:12.043-05:00 -> Limit (cost=0.05..0.06 rows=1 width=72) (actual time=7895.267..7895.283 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 InitPlan 8 (returns $11) 2021-02-09T00:39:12.043-05:00 -> CTE Scan on totalcount (cost=0.00..0.02 rows=1 width=8) (actual time=523.176..523.177 rows=1 loops=1) 2021-02-09T00:39:12.043-05:00 -> Sort (cost=0.03..0.04 rows=1 width=72) (actual time=7895.265..7895.272 rows=100 loops=1) 2021-02-09T00:39:12.043-05:00 Sort Key: allrecords.data_column 2021-02-09T00:39:12.043-05:00 Sort Method: top-N heapsort Memory: 62kB 2021-02-09T00:39:12.043-05:00 -> CTE Scan on allrecords (cost=0.00..0.02 rows=1 width=72) (actual time=531.970..7880.180 rows=16412 loops=1) 2021-02-09T00:39:12.043-05:00 Planning time: 1.955 ms 2021-02-09T00:39:12.043-05:00 Execution time: 14463.015 ms