2

This is using RDS Postgres 9.5.2

We have a table which contains about 25 columns, and one is a jsonb col that essentially is a simple/flat json object (key/value, not complex objects). The data in the jsonb column is a mix of types as well as number of keys and length of data. This has made an in-place altering to col to an hstore impossible thus far due to the length of the data stored in there.

At 3.5 mil rows, querying the jsonb col is creating a performance bottleneck and I'm unsure if there's a way around it. Our queries can use the jsonb in a select, as a filter, or even a group by. Here is an example of it in a select (with analyze):

EXPLAIN (ANALYZE, BUFFERS)
SELECT submit_time
        , answers->'QSatR3_value' AS "answers__QSatR3_value"
        , answers->'QSatR6_value' AS "answers__QSatR6_value"
        , answers->'QSatOR7_value' AS "answers__QSatOR7_value"
        , answers->'QSatR2_value' AS "answers__QSatR2_value"
        , answers->'QSatStaffR1_value' AS "answers__QSatStaffR1_value"
        , answers->'QSatStaffR2_value' AS "answers__QSatStaffR2_value"
        , answers->'QSatStaffR3_value' AS "answers__QSatStaffR3_value" 
    FROM response
     WHERE fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid
         AND status IN ('Completed','Partial','Invited')
         AND submit_time BETWEEN '2015-01-01' AND '2015-12-31'

In this example fk_id, submit_time are both have b-tree indexing as well as a gin index on the jsonb col flatten.

EXPLAIN ANALYZE (with BUFFERS):

Bitmap Heap Scan on response  (cost=23930.35..252834.17 rows=62340 width=156) (actual time=226.943..12643.450 rows=388668 loops=1)
  Recheck Cond: ((fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid) AND (submit_time >= '2015-01-01 00:00:00+00'::timestamp with time zone) AND (submit_time <= '2015-12-31 00:00:00+00'::timestamp with time zone))
  Filter: (status = ANY ('{Completed,Partial,Invited}'::text[]))
  Heap Blocks: exact=149116
  Buffers: shared hit=4148144
  ->  BitmapAnd  (cost=23930.35..23930.35 rows=83039 width=0) (actual time=194.092..194.092 rows=0 loops=1)
        Buffers: shared hit=3443
        ->  Bitmap Index Scan on customer_00b3bd7e  (cost=0.00..8961.86 rows=391124 width=0) (actual time=48.794..48.794 rows=389534 loops=1)
              Index Cond: (fk_id = '0d95d6bd-f437-483c-8bbc-a38f11990153'::uuid)
              Buffers: shared hit=1495
        ->  Bitmap Index Scan on response_submit_time_7262d9fd_uniq  (cost=0.00..14937.07 rows=712064 width=0) (actual time=113.067..113.067 rows=711760 loops=1)
              Index Cond: ((submit_time >= '2015-01-01 00:00:00+00'::timestamp with time zone) AND (submit_time <= '2015-12-31 00:00:00+00'::timestamp with time zone))
              Buffers: shared hit=1948
Planning time: 0.190 ms
Execution time: 12736.179 ms

At the end of the day, each time a select of a column is added it adds maybe 2.5 seconds to the overall query, so larger queries can take upwards of 60 seconds to return. Since the data in the jsonb col is variable I cannot simply add indicies for the keys, but I can add four or five of what seem to be common keys used in queries.

Is there anything else that can be done to speed this up? I cannot create use the FK UUID field in an index as RDS does not allow extensions to be added, so I cannot try that. Is there anything that I can within the jsonb index or even how we are fetching the data?

Update: As to why we are returning so many rows: this data sits under our reporting platform, and the result sets are passed into python modules that create desired statistical metrics using the data.

The actual result set of this query is about 398k which is then distilled into the desired statistical metrics and cuts. We have not had a problem with the speed until we recently loaded an additional ~1 million records. I'm working to understand if we can push the metrics calcs to the database, but that's a much larger change across our platform.

  • 1
    What are you doing with so many rows? Can't you somehow decrease the size of the returned set? – dezso Mar 17 '17 at 16:11
  • 1
    Is a select statement really necessary if it's returning 388,668? are you really using a result set that big? – Evan Carroll Mar 17 '17 at 16:11
  • 1
    Try an index on (fk_id, submit_time) – a_horse_with_no_name Mar 17 '17 at 16:17
  • Try also an index on (fk_id, submit_time, status) – joanolo Mar 17 '17 at 18:24
  • 1
    If all your jsonb have an 'QSatR3_value' attribute (and so on). I'd convert those into normalized columns, and out of a jsonb. If the jsonb objects are large, accessing their individual attributes becomes costly (less if they're actually jsonB and not plain json) – joanolo Mar 17 '17 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.