Best approach to get ordered data from big table

Viewed 140

I am trying to get ordered and filtered data from a 10 million row table. The problem is very slow ordered by working in this case. The number of filtering combinations is very large, I cannot predict all of them to create the cache with the correct order. Also, my data can be changed often. In other words I really need to sort my data with every query.

I tried to google it and realized that indexes and other optimizations couldn't be helpful for ordering. Am I right? And what is the best way to get ordered data quickly? Is it possible in postgres? Should I use different tools for that? Maybe ETL in non-relational db or smth like that?

example of simple query:

EXPLAIN ANALYZE
SELECT *
FROM offers
WHERE
        offers.attributes ->> 'width' = '190'
    AND offers.attributes ->> 'height' = '55'
    AND offers.attributes ->> 'diameter' = '16'
ORDER BY price

explain analyze output:

Sort  (cost=463529.78..463529.78 rows=1 width=248) (actual time=3083.447..3084.143 rows=12420 loops=1)
  Sort Key: price
  Sort Method: quicksort  Memory: 6530kB
  ->  Gather  (cost=1000.00..463529.77 rows=1 width=248) (actual time=0.327..3078.755 rows=12420 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Parallel Seq Scan on offers  (cost=0.00..462529.67 rows=1 width=248) (actual time=4.826..3072.672 rows=4140 loops=3)
              Filter: (((attributes ->> 'width'::text) = '190'::text) AND ((attributes ->> 'height'::text) = '55'::text) AND ((attributes ->> 'diameter'::text) = '16'::text))
              Rows Removed by Filter: 3329193
Planning Time: 0.256 ms
Execution Time: 3084.612 ms

all table indexes

create index offers_attribu_5d15b4_gin
    on offers using gin (attributes);

create index offers_created_ae6fa523
    on offers (created);

create index offers_updated_c723e738
    on offers (updated);

create index offers_quantity_dc0026a6
    on offers (quantity);

create index offers_model_id_8846a913
    on offers (model_id);

create index offers_price_list_id_1f3d95de
    on offers (price_list_id);

create index offers_price_ids
    on offers (price);

create index offers_model_price_idx
    on offers (model_id, price);

create index offers_json_price_idx
    on offers (attributes, price);

create index offers_json_model_idx
    on offers (attributes, model_id);

create index offers_price_idx
    on offers (price);
1 Answers

Your query will become must faster if you rewrite it so that it can use the GIN index on the jsonb column:

SELECT *
FROM offers
WHERE attributes @> '{ "width": 190, "height": 55, "diameter": 16 }'
ORDER BY price;
Related