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);