I have a total of over 6,000,000 rows of data in this table, after filtering it by where condition, there are about 120,000 rows of data.

Currently I have created two multi-column indexers

CREATE INDEX "IDX_module_method_height" ON "events" ("module", "method", "block_height")

CREATE INDEX "IDX_module_method" ON "events" ("module", "method")

When I run the following sql and it was really fast

The fast one:

explain analyze Select block_height from events where (module='amm' and method in ('Traded', 'LiquidityAdded')) order by block_height desc limit 500 offset 200;


"Limit  (cost=2748.32..2749.57 rows=500 width=4) (actual time=51.207..51.288 rows=500 loops=1)"
"  ->  Sort  (cost=2747.82..2757.85 rows=4010 width=4) (actual time=51.183..51.236 rows=700 loops=1)"
"        Sort Key: block_height DESC"
"        Sort Method: top-N heapsort  Memory: 81kB"
"        ->  Index Only Scan using ""IDX_module_method_height"" on events  (cost=0.56..2538.28 rows=4010 width=4) (actual time=0.061..35.880 rows=128860 loops=1)"
"              Index Cond: ((method = ANY ('{Traded,LiquidityAdded}'::text[])) AND (module = 'amm'::text))"
"              Heap Fetches: 17403"
"Planning Time: 0.212 ms"
"Execution Time: 51.344 ms"

But when I add one more selected column (eg: data), it is really slow, but i really need data column.

The slow one (just add one more data field in select)

explain analyze Select block_height, data from events where (module='amm' and method in ('Traded', 'LiquidityAdded')) order by block_height desc limit 500 offset 200;

"Limit  (cost=14459.53..14460.78 rows=500 width=133) (actual time=12061.968..12062.068 rows=500 loops=1)"
"  ->  Sort  (cost=14459.03..14469.06 rows=4011 width=133) (actual time=12061.935..12062.012 rows=700 loops=1)"
"        Sort Key: block_height DESC"
"        Sort Method: top-N heapsort  Memory: 371kB"
"        ->  Index Scan using "IDX_module_method" on events  (cost=0.43..14249.43 rows=4011 width=133) (actual time=1.302..12014.625 rows=128860 loops=1)"
"              Index Cond: (((module)::text = 'amm'::text) AND ((method)::text = ANY ('{Traded,LiquidityAdded}'::text[])))"
"Planning Time: 0.144 ms"
"Execution Time: 12063.364 ms"

Why do select columns affect the selection of indexes, I need data fields and how should I create indexes to make sql efficient?

CodePudding user response：

reason is that it needs to do table lookup. your WHERE clause can be solved by both indices, the smaller is faster. with single field it could do INDEX ONLY SCAN, now it does INDEX SCAN.

1. you can try to make your index fit "better"

CREATE INDEX "IDX_module_method_height_desc" ON "events" ("module", "method", "block_height" DESC)

2. if your table have primary key, you could make your table lookup small with index only scan and LIMIT first.

SELECT block_height, data
FROM events
JOIN (
  SELECT id
  FROM events
  WHERE ...
  ORDER BY block_height DESC
  LIMIT 500 OFFSET 200) x USING (id)

3. otherwise as others suggested you can extend your index with "include" if you use Postgres 11 or newer

CREATE INDEX "IDX_module_method_height_desc" ON "events" ("module", "method", "block_height" DESC) INCLUDE ("data")