Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6BC4-0006ut-2O for pgsql-performance@arkaria.postgresql.org; Fri, 16 Dec 2022 13:56:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p6BC2-0003fp-W5 for pgsql-performance@arkaria.postgresql.org; Fri, 16 Dec 2022 13:56:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6Ani-0003NV-7m for pgsql-performance@lists.postgresql.org; Fri, 16 Dec 2022 13:30:58 +0000 Received: from mout.kundenserver.de ([212.227.126.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p6Anf-0001Re-An for pgsql-performance@lists.postgresql.org; Fri, 16 Dec 2022 13:30:57 +0000 Received: from smtpclient.apple ([37.135.213.61]) by mrelayeu.kundenserver.de (mreue010 [213.165.67.102]) with ESMTPSA (Nemesis) id 1MlNcr-1ocrjM1djp-00ljTP for ; Fri, 16 Dec 2022 14:30:53 +0100 From: Render Comunicacion S.L. Content-Type: multipart/alternative; boundary="Apple-Mail=_BEBFAA0C-B3E6-479E-9A18-517040F08F85" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.200.110.1.12\)) Subject: JSON down performacen when id:1 Message-Id: Date: Fri, 16 Dec 2022 14:30:42 +0100 To: pgsql-performance@lists.postgresql.org X-Mailer: Apple Mail (2.3731.200.110.1.12) X-Provags-ID: V03:K1:9LRqsYNNvio1yWssiu0BysgxepFMBDaqtsa3tPbhHuJEdYZP/Ak qQPLoHuFzhXfvHTWXz8TlITCLzbe8kDnbLBXMp8CT16u7ksIA/RM6Apmqr2RsSd3flNrUEC /un50cHUWh5mpeymcGPJ//wAnNamwJ1vqLWCbCA5+GHgpxkmwu10wZCccJa/S90gDbJ/3v9 BJkbC98PUCav1eJlmwEGw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:IlbVgMCFLdw=;24y0lV5gk6kzmoKkK1ZH0OhjnDJ nohc5BedBUl5E+5o1Lo6HKYX1fuiT/LS/yY3UyNs91W2y8onbnLyWbs1NxiYYoH2laIvPecWL UMe17uXHcJxDFluEVyjuYfoH653suhqewAq8gING0KXpoG/MsNpem1VA8YLzwnzvYY/AUep7a 6HiIchIPEKqhrAgf0KRqZ4vkzuWyvQ+xG4vD17Vzgy+T7K6AWy3nFWfRiH6Axv+fBLO9aex3N tqfHNxfPtnSdzaZ61V9lZ9EncZv/m/vQktx9HCrI+dyTbfnVDSwy3N7jtWEgjm5Jcu8UCHmr0 /p3dRbcrtBEyXwl+HoIW44STLiNZ1jk+/59RBEyRTrICdbJwDy6725SvLX8cTdH6XXiodPcsv 8u118siqNG54fGv8BVFhWDlQqbHn7uTd+mY7YBlzdg+jBM2yu/bPkre/6xyMpuPJQofNRNJ8J SYNji0H7GAyXcjIGEpfO0dxA6Td1ULvmA9pt+MIH+vsqQnpY0EAaktdPtkpyW5ksiqYk109No 9cC1tPpn5sFBQy4w6QgXODcYGFfaHm1Bg0LKg9EabVJmDx7fxiprqUiz0jKoPBmD7VDYZvyhl EHHOiXSJbJZaiAIxlDztLevfeLTdIqWI0mNdUSZA4sBDQzGgdF1BjONC8v7sD1yhGuajY/w/x T8z1fCPXq/2g4j/2i0hnljpxiQHnjawFnC1+n38nBA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_BEBFAA0C-B3E6-479E-9A18-517040F08F85 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hello to everyone I'm new here, and I hope that my question is on the correct mail-list. We use PostgreSQL to store JSON-B in different tables, all tables have = the same schema and all tables are indexed with GIN index for the JSON = data. We use two properties of the JSON to locate data: { "section_id":"1", "section_tipo":"numisdata3" } The issue: When we search our locator with section_id: 1 (or any number < 4), = PostgreSQL takes around 40000, 5000, 8000ms or more. When we search our locator with section_id: 4 (or any other bigger = number), PostgreSQL takes around 100 ms. ( ~ expected time) Next queries are done in a database with +/- 1 million of rows in total, = and we tested in PostgreSQL 13,14 and 15 with similar results. _____________________________________________ The query for section_id: 1 (13 rows) EXPLAIN ANALYZE SELECT section_tipo, section_id, datos FROM "matrix" WHERE ( datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_activities" WHERE ( datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_hierarchy" WHERE ( datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_list" WHERE ( datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb) ORDER BY section_tipo, section_id ASC LIMIT ALL; QUERY PLAN Sort (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D8752.794..8752.797 rows=3D13 loops=3D1) Sort Key: matrix.section_tipo, matrix.section_id Sort Method: quicksort Memory: 47kB -> Append (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D415.709..8741.565 rows=3D13 loops=3D1) -> Bitmap Heap Scan on matrix (cost=3D92.21..199.36 rows=3D27 = width=3D1144) (actual time=3D415.708..8325.296 rows=3D11 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 272037 Heap Blocks: exact=3D34164 lossy=3D33104 -> Bitmap Index Scan on matrix_relations_idx = (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D61.462..61.462 = rows=3D155031 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) -> Seq Scan on matrix_activities (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.012..0.012 rows=3D0 loops=3D1) Filter: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_hierarchy (cost=3D52.26..8492.67 = rows=3D2614 width=3D1362) (actual time=3D269.624..414.954 rows=3D2 = loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 5043 Heap Blocks: exact=3D3034 -> Bitmap Index Scan on matrix_hierarchy_relations_idx = (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual time=3D9.529..9.529 = rows=3D5049 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_list (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D1.260..1.260 rows=3D0 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Index Scan on matrix_list_relations_idx = (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D1.258..1.258 = rows=3D0 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "1", "section_tipo": "numisdata3"}]'::jsonb) Planning Time: 33.625 ms Execution Time: 8753.461 ms _____________________________________________ The query for section_id: 2 (18 rows) EXPLAIN ANALYZE SELECT section_tipo, section_id, datos FROM "matrix" WHERE ( datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_activities" WHERE ( datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_hierarchy" WHERE ( datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_list" WHERE ( datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb) ORDER BY section_tipo, section_id ASC LIMIT ALL; Sort (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D5236.090..5236.097 rows=3D18 loops=3D1) Sort Key: matrix.section_tipo, matrix.section_id Sort Method: quicksort Memory: 57kB -> Append (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D200.244..5235.964 rows=3D18 loops=3D1) -> Bitmap Heap Scan on matrix (cost=3D92.21..199.36 rows=3D27 = width=3D1144) (actual time=3D200.244..5188.015 rows=3D16 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 270935 Heap Blocks: exact=3D33885 lossy=3D33106 -> Bitmap Index Scan on matrix_relations_idx = (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D51.763..51.764 = rows=3D153659 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) -> Seq Scan on matrix_activities (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.022..0.022 rows=3D0 loops=3D1) Filter: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_hierarchy (cost=3D52.26..8492.67 = rows=3D2614 width=3D1362) (actual time=3D5.112..47.171 rows=3D2 loops=3D1)= Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 2479 Heap Blocks: exact=3D1805 -> Bitmap Index Scan on matrix_hierarchy_relations_idx = (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual time=3D4.834..4.834 = rows=3D2484 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_list (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.738..0.738 rows=3D0 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Index Scan on matrix_list_relations_idx = (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.735..0.735 = rows=3D0 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "2", "section_tipo": "numisdata3"}]'::jsonb) Planning Time: 30.869 ms Execution Time: 5236.796 ms _____________________________________________ The query for section_id: 3 (7 rows) EXPLAIN ANALYZE SELECT section_tipo, section_id, datos FROM "matrix" WHERE ( datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_activities" WHERE ( datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_hierarchy" WHERE ( datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_list" WHERE ( datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb) ORDER BY section_tipo, section_id ASC LIMIT ALL; Sort (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D1796.808..1796.813 rows=3D7 loops=3D1) Sort Key: matrix.section_tipo, matrix.section_id Sort Method: quicksort Memory: 36kB -> Append (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D114.715..1796.731 rows=3D7 loops=3D1) -> Bitmap Heap Scan on matrix (cost=3D92.21..199.36 rows=3D27 = width=3D1144) (actual time=3D114.714..1403.005 rows=3D6 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 63200 Heap Blocks: exact=3D39788 -> Bitmap Index Scan on matrix_relations_idx = (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D52.239..52.239 = rows=3D63248 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) -> Seq Scan on matrix_activities (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.018..0.018 rows=3D0 loops=3D1) Filter: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_hierarchy (cost=3D52.26..8492.67 = rows=3D2614 width=3D1362) (actual time=3D329.263..392.708 rows=3D1 = loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 4925 Heap Blocks: exact=3D2996 -> Bitmap Index Scan on matrix_hierarchy_relations_idx = (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual time=3D6.059..6.059 = rows=3D4930 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_list (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.988..0.988 rows=3D0 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Index Scan on matrix_list_relations_idx = (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.985..0.985 = rows=3D0 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "3", "section_tipo": "numisdata3"}]'::jsonb) Planning Time: 4.339 ms Execution Time: 1797.240 ms _____________________________________________ The query for section_id: 4 (6 rows) EXPLAIN ANALYZE SELECT section_tipo, section_id, datos FROM "matrix" WHERE ( datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_activities" WHERE ( datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_hierarchy" WHERE ( datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_list" WHERE ( datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb) ORDER BY section_tipo, section_id ASC LIMIT ALL; QUERY PLAN Sort (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D25.171..25.174 rows=3D6 loops=3D1) Sort Key: matrix.section_tipo, matrix.section_id Sort Method: quicksort Memory: 34kB -> Append (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D6.227..25.112 rows=3D6 loops=3D1) -> Bitmap Heap Scan on matrix (cost=3D92.21..199.36 rows=3D27 = width=3D1144) (actual time=3D6.227..24.955 rows=3D4 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 1758 Heap Blocks: exact=3D1469 -> Bitmap Index Scan on matrix_relations_idx = (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D3.139..3.139 = rows=3D1765 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) -> Seq Scan on matrix_activities (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.010..0.010 rows=3D0 loops=3D1) Filter: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_hierarchy (cost=3D52.26..8492.67 = rows=3D2614 width=3D1362) (actual time=3D0.101..0.126 rows=3D2 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 1 Heap Blocks: exact=3D3 -> Bitmap Index Scan on matrix_hierarchy_relations_idx = (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual time=3D0.088..0.088 = rows=3D3 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_list (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.015..0.015 rows=3D0 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Index Scan on matrix_list_relations_idx = (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.015..0.015 = rows=3D0 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": "numisdata3"}]'::jsonb) Planning Time: 3.579 ms Execution Time: 25.278 ms _____________________________________________ The query for section_id: 5 (24 rows) EXPLAIN ANALYZE SELECT section_tipo, section_id, datos FROM "matrix" WHERE ( datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_activities" WHERE ( datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_hierarchy" WHERE ( datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb) UNION ALL SELECT section_tipo, section_id, datos FROM "matrix_list" WHERE ( datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb) ORDER BY section_tipo, section_id ASC LIMIT ALL; QUERY PLAN Sort (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D111.243..111.249 rows=3D28 loops=3D1) Sort Key: matrix.section_tipo, matrix.section_id Sort Method: quicksort Memory: 69kB -> Append (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D13.804..111.086 rows=3D28 loops=3D1) -> Bitmap Heap Scan on matrix (cost=3D92.21..199.36 rows=3D27 = width=3D1144) (actual time=3D13.803..108.578 rows=3D26 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 5967 Heap Blocks: exact=3D4691 -> Bitmap Index Scan on matrix_relations_idx = (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D11.815..11.815 = rows=3D6000 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) -> Seq Scan on matrix_activities (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.011..0.011 rows=3D0 loops=3D1) Filter: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_hierarchy (cost=3D52.26..8492.67 = rows=3D2614 width=3D1362) (actual time=3D2.034..2.052 rows=3D2 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) Rows Removed by Index Recheck: 1 Heap Blocks: exact=3D3 -> Bitmap Index Scan on matrix_hierarchy_relations_idx = (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual time=3D1.987..1.987 = rows=3D4 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Heap Scan on matrix_list (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.426..0.426 rows=3D0 loops=3D1) Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) -> Bitmap Index Scan on matrix_list_relations_idx = (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.418..0.418 = rows=3D0 loops=3D1) Index Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": "numisdata3"}]'::jsonb) Planning Time: 4.328 ms Execution Time: 111.514 ms _____________________________________________ We have checked the index and it's ok, we did vacuum, vacuum analyze...=20= We can understand that the first 3 searches for section_id didn't use = the index... But... how can we fix it?=20 Thanks! Best Alex alex@render.es 657661974 =C2=B7 Denia 50, bajo izquierda =C2=B7 46006 =C2=B7 Valencia --Apple-Mail=_BEBFAA0C-B3E6-479E-9A18-517040F08F85 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Hello to = everyone

I'm new here, and I hope that my question is = on the correct mail-list.

We use PostgreSQL to = store JSON-B in different tables, all tables have the same schema and = all tables are indexed with GIN index for the JSON = data.

We use two properties of the JSON to = locate data:

{
= "section_id":"1",
= "section_tipo":"numisdata3"
}

Th= e issue:
When we search our locator with section_id: 1 (or any = number < 4), PostgreSQL takes around 40000, 5000, 8000ms or = more.
When we search our locator with section_id: 4 (or any = other bigger number), PostgreSQL takes around 100 ms. ( ~ expected = time)

Next queries are done in a database with = +/- 1 million of rows in total, and we tested in PostgreSQL 13,14 and 15 = with similar = results.

________________________________________= _____

The query for section_id: 1 (13 = rows)

EXPLAIN ANALYZE SELECT section_tipo, = section_id, datos
FROM "matrix"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_activities"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_hierarchy"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
 SELECT section_tipo, section_id, = datos
FROM "matrix_list"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"1","section_tipo":"numisdata3"}]'::jsonb)
ORDER= BY section_tipo, section_id ASC
LIMIT = ALL;

QUERY PLAN
Sort =  (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D8752.794..8752.797 rows=3D13 loops=3D1)
  Sort = Key: matrix.section_tipo, matrix.section_id
  Sort = Method: quicksort  Memory: 47kB
  ->  Append =  (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D415.709..8741.565 rows=3D13 loops=3D1)
    =     ->  Bitmap Heap Scan on matrix =  (cost=3D92.21..199.36 rows=3D27 width=3D1144) (actual = time=3D415.708..8325.296 rows=3D11 loops=3D1)
    =           Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 272037
  =             Heap Blocks: exact=3D34164 = lossy=3D33104
              = ->  Bitmap Index Scan on matrix_relations_idx =  (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual = time=3D61.462..61.462 rows=3D155031 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "1", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Seq Scan on matrix_activities  (cost=3D0.00..0.00 = rows=3D1 width=3D68) (actual time=3D0.012..0.012 rows=3D0 = loops=3D1)
              = Filter: ((datos #> '{relations}'::text[]) @> '[{"section_id": "1", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_hierarchy =  (cost=3D52.26..8492.67 rows=3D2614 width=3D1362) (actual = time=3D269.624..414.954 rows=3D2 loops=3D1)
    =           Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 5043
  =             Heap Blocks: = exact=3D3034
              = ->  Bitmap Index Scan on matrix_hierarchy_relations_idx =  (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual = time=3D9.529..9.529 rows=3D5049 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "1", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_list =  (cost=3D12.21..100.53 rows=3D27 width=3D1161) (actual = time=3D1.260..1.260 rows=3D0 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": = "numisdata3"}]'::jsonb)
          =     ->  Bitmap Index Scan on matrix_list_relations_idx =  (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D1.258..1.258= rows=3D0 loops=3D1)
            =         Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "1", "section_tipo": = "numisdata3"}]'::jsonb)
Planning Time: 33.625 = ms
Execution Time: 8753.461 = ms

_______________________________= ______________

The query = for section_id: 2 (18 = rows)


EXPLAIN ANALYZE = SELECT section_tipo, section_id, datos
FROM = "matrix"
WHERE (
datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_activities"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_hierarchy"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
 SELECT section_tipo, section_id, = datos
FROM "matrix_list"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"2","section_tipo":"numisdata3"}]'::jsonb)
ORDER= BY section_tipo, section_id ASC
LIMIT = ALL;

Sort =  (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D5236.090..5236.097 rows=3D18 loops=3D1)
  Sort = Key: matrix.section_tipo, matrix.section_id
  Sort = Method: quicksort  Memory: 57kB
  ->  Append =  (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D200.244..5235.964 rows=3D18 loops=3D1)
    =     ->  Bitmap Heap Scan on matrix =  (cost=3D92.21..199.36 rows=3D27 width=3D1144) (actual = time=3D200.244..5188.015 rows=3D16 loops=3D1)
    =           Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "2", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 270935
  =             Heap Blocks: exact=3D33885 = lossy=3D33106
              = ->  Bitmap Index Scan on matrix_relations_idx =  (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual = time=3D51.763..51.764 rows=3D153659 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "2", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Seq Scan on matrix_activities  (cost=3D0.00..0.00 = rows=3D1 width=3D68) (actual time=3D0.022..0.022 rows=3D0 = loops=3D1)
              = Filter: ((datos #> '{relations}'::text[]) @> '[{"section_id": "2", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_hierarchy =  (cost=3D52.26..8492.67 rows=3D2614 width=3D1362) (actual = time=3D5.112..47.171 rows=3D2 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "2", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 2479
  =             Heap Blocks: = exact=3D1805
              = ->  Bitmap Index Scan on matrix_hierarchy_relations_idx =  (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual = time=3D4.834..4.834 rows=3D2484 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "2", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_list =  (cost=3D12.21..100.53 rows=3D27 width=3D1161) (actual = time=3D0.738..0.738 rows=3D0 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "2", "section_tipo": = "numisdata3"}]'::jsonb)
          =     ->  Bitmap Index Scan on matrix_list_relations_idx =  (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.735..0.735= rows=3D0 loops=3D1)
            =         Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "2", "section_tipo": = "numisdata3"}]'::jsonb)
Planning Time: 30.869 = ms
Execution Time: 5236.796 = ms

____________________________________= _________

The query for section_id: = 3 (7 rows)

EXPLAIN ANALYZE SELECT = section_tipo, section_id, datos
FROM "matrix"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_activities"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_hierarchy"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
 SELECT section_tipo, section_id, = datos
FROM "matrix_list"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"3","section_tipo":"numisdata3"}]'::jsonb)
ORDER= BY section_tipo, section_id ASC
LIMIT = ALL;

Sort =  (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D1796.808..1796.813 rows=3D7 loops=3D1)
  Sort Key: = matrix.section_tipo, matrix.section_id
  Sort Method: = quicksort  Memory: 36kB
  ->  Append =  (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D114.715..1796.731 rows=3D7 loops=3D1)
    =     ->  Bitmap Heap Scan on matrix =  (cost=3D92.21..199.36 rows=3D27 width=3D1144) (actual = time=3D114.714..1403.005 rows=3D6 loops=3D1)
    =           Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "3", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 63200
  =             Heap Blocks: = exact=3D39788
              = ->  Bitmap Index Scan on matrix_relations_idx =  (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual = time=3D52.239..52.239 rows=3D63248 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "3", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Seq Scan on matrix_activities  (cost=3D0.00..0.00 = rows=3D1 width=3D68) (actual time=3D0.018..0.018 rows=3D0 = loops=3D1)
              = Filter: ((datos #> '{relations}'::text[]) @> '[{"section_id": "3", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_hierarchy =  (cost=3D52.26..8492.67 rows=3D2614 width=3D1362) (actual = time=3D329.263..392.708 rows=3D1 loops=3D1)
    =           Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "3", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 4925
  =             Heap Blocks: = exact=3D2996
              = ->  Bitmap Index Scan on matrix_hierarchy_relations_idx =  (cost=3D0.00..51.61 rows=3D2614 width=3D0) (actual = time=3D6.059..6.059 rows=3D4930 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "3", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_list =  (cost=3D12.21..100.53 rows=3D27 width=3D1161) (actual = time=3D0.988..0.988 rows=3D0 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "3", "section_tipo": = "numisdata3"}]'::jsonb)
          =     ->  Bitmap Index Scan on matrix_list_relations_idx =  (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.985..0.985= rows=3D0 loops=3D1)
            =         Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "3", "section_tipo": = "numisdata3"}]'::jsonb)
Planning Time: 4.339 = ms
Execution Time: 1797.240 = ms

____________________________________= _________

The query for section_id: 4 (6 = rows)

EXPLAIN ANALYZE SELECT section_tipo, = section_id, datos
FROM "matrix"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_activities"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_hierarchy"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
 SELECT section_tipo, section_id, = datos
FROM "matrix_list"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"4","section_tipo":"numisdata3"}]'::jsonb)
ORDER= BY section_tipo, section_id ASC
LIMIT = ALL;

QUERY PLAN
Sort =  (cost=3D8984.49..8991.16 rows=3D2669 width=3D1357) (actual = time=3D25.171..25.174 rows=3D6 loops=3D1)
  Sort Key: = matrix.section_tipo, matrix.section_id
  Sort Method: = quicksort  Memory: 34kB
  ->  Append =  (cost=3D92.21..8832.59 rows=3D2669 width=3D1357) (actual = time=3D6.227..25.112 rows=3D6 loops=3D1)
      =   ->  Bitmap Heap Scan on matrix  (cost=3D92.21..199.36 = rows=3D27 width=3D1144) (actual time=3D6.227..24.955 rows=3D4 = loops=3D1)
              = Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 1758
  =             Heap Blocks: = exact=3D1469
              = ->  Bitmap Index Scan on matrix_relations_idx =  (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual time=3D3.139..3.139= rows=3D1765 loops=3D1)
          =           Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
        -> =  Seq Scan on matrix_activities  (cost=3D0.00..0.00 rows=3D1 = width=3D68) (actual time=3D0.010..0.010 rows=3D0 = loops=3D1)
              = Filter: ((datos #> '{relations}'::text[]) @> '[{"section_id": "4", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_hierarchy =  (cost=3D52.26..8492.67 rows=3D2614 width=3D1362) (actual = time=3D0.101..0.126 rows=3D2 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 1
    =           Heap Blocks: exact=3D3
 =             ->  Bitmap Index Scan = on matrix_hierarchy_relations_idx  (cost=3D0.00..51.61 rows=3D2614 = width=3D0) (actual time=3D0.088..0.088 rows=3D3 = loops=3D1)
              =       Index Cond: ((datos #> '{relations}'::text[]) = @> '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
        -> =  Bitmap Heap Scan on matrix_list  (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.015..0.015 rows=3D0 = loops=3D1)
              = Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
          =     ->  Bitmap Index Scan on matrix_list_relations_idx =  (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.015..0.015= rows=3D0 loops=3D1)
            =         Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "4", "section_tipo": = "numisdata3"}]'::jsonb)
Planning Time: 3.579 = ms
Execution Time: 25.278 = ms

____________________________________= _________

The query for section_id: 5 = (24 rows)

EXPLAIN ANALYZE SELECT = section_tipo, section_id, datos
FROM "matrix"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_activities"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
SELECT section_tipo, section_id, datos
FROM = "matrix_hierarchy"
WHERE (
datos#>'{relations}' = @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb)
UNION= ALL
 SELECT section_tipo, section_id, = datos
FROM "matrix_list"
WHERE = (
datos#>'{relations}' @> = '[{"section_id":"5","section_tipo":"numisdata3"}]'::jsonb)
ORDER= BY section_tipo, section_id ASC
LIMIT = ALL;


QUERY = PLAN
Sort  (cost=3D8984.49..8991.16 rows=3D2669 = width=3D1357) (actual time=3D111.243..111.249 rows=3D28 = loops=3D1)
  Sort Key: matrix.section_tipo, = matrix.section_id
  Sort Method: quicksort  Memory: = 69kB
  ->  Append  (cost=3D92.21..8832.59 = rows=3D2669 width=3D1357) (actual time=3D13.804..111.086 rows=3D28 = loops=3D1)
        ->  Bitmap Heap = Scan on matrix  (cost=3D92.21..199.36 rows=3D27 width=3D1144) = (actual time=3D13.803..108.578 rows=3D26 loops=3D1)
  =             Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "5", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 5967
  =             Heap Blocks: = exact=3D4691
              = ->  Bitmap Index Scan on matrix_relations_idx =  (cost=3D0.00..92.20 rows=3D27 width=3D0) (actual = time=3D11.815..11.815 rows=3D6000 loops=3D1)
    =                 Index Cond: = ((datos #> '{relations}'::text[]) @> '[{"section_id": "5", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Seq Scan on matrix_activities  (cost=3D0.00..0.00 = rows=3D1 width=3D68) (actual time=3D0.011..0.011 rows=3D0 = loops=3D1)
              = Filter: ((datos #> '{relations}'::text[]) @> '[{"section_id": "5", = "section_tipo": "numisdata3"}]'::jsonb)
      =   ->  Bitmap Heap Scan on matrix_hierarchy =  (cost=3D52.26..8492.67 rows=3D2614 width=3D1362) (actual = time=3D2.034..2.052 rows=3D2 loops=3D1)
      =         Recheck Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "5", "section_tipo": = "numisdata3"}]'::jsonb)
          =     Rows Removed by Index Recheck: 1
    =           Heap Blocks: exact=3D3
 =             ->  Bitmap Index Scan = on matrix_hierarchy_relations_idx  (cost=3D0.00..51.61 rows=3D2614 = width=3D0) (actual time=3D1.987..1.987 rows=3D4 = loops=3D1)
              =       Index Cond: ((datos #> '{relations}'::text[]) = @> '[{"section_id": "5", "section_tipo": = "numisdata3"}]'::jsonb)
        -> =  Bitmap Heap Scan on matrix_list  (cost=3D12.21..100.53 = rows=3D27 width=3D1161) (actual time=3D0.426..0.426 rows=3D0 = loops=3D1)
              = Recheck Cond: ((datos #> '{relations}'::text[]) @> = '[{"section_id": "5", "section_tipo": = "numisdata3"}]'::jsonb)
          =     ->  Bitmap Index Scan on matrix_list_relations_idx =  (cost=3D0.00..12.21 rows=3D27 width=3D0) (actual time=3D0.418..0.418= rows=3D0 loops=3D1)
            =         Index Cond: ((datos #> = '{relations}'::text[]) @> '[{"section_id": "5", "section_tipo": = "numisdata3"}]'::jsonb)
Planning Time: 4.328 = ms
Execution Time: 111.514 = ms

____________________________________= _________

We have checked the index and it's = ok, we did vacuum, vacuum analyze... 

We = can understand that the first 3 searches for section_id didn't use the = index... But... how can we fix = it? 

Thanks!

Best
Alex
alex@render.es


657661974 =C2=B7 Denia 50, = bajo izquierda =C2=B7 46006 =C2=B7 Valencia



= --Apple-Mail=_BEBFAA0C-B3E6-479E-9A18-517040F08F85--