I have the following table:
CREATE TABLE dpg2
account_id integer NOT NULL,
tank_id integer NOT NULL,
recent_ts timestamp with time zone,
CONSTRAINT dpg2_pkey PRIMARY KEY (account_id, tank_id)
with this index:
CREATE INDEX dpg_tank_id_idx
(tank_id, dpg DESC NULLS LAST);
I ran the following query:
select dpg2.account_id, tank_id, (select nickname from players2 where players2.account_id = dpg2.account_id), dpg2.battles, dpg,
frags*1.0/dpg2.battles, wins*100.0/dpg2.battles, dpg2.recent_dpg
where tank_id=545 and battles >= 150
order by dpg desc
with the following output:
"Limit (cost=1523898.99..1523899.12 rows=50 width=28) (actual time=23950.831..23950.838 rows=50 loops=1)"
" -> Sort (cost=1523898.99..1524200.69 rows=120678 width=28) (actual time=23950.831..23950.833 rows=50 loops=1)"
" Sort Key: dpg2.dpg"
" Sort Method: top-N heapsort Memory: 32kB"
" -> Bitmap Heap Scan on dpg2 (cost=13918.06..1519890.16 rows=120678 width=28) (actual time=434.791..23922.872 rows=21963 loops=1)"
" Recheck Cond: (tank_id = 545)"
" Filter: (battles >= 150)"
" Rows Removed by Filter: 1060576"
" Heap Blocks: exact=458918"
" -> Bitmap Index Scan on dpg_tank_id_idx (cost=0.00..13887.89 rows=967310 width=0) (actual time=299.796..299.796 rows=1082539 loops=1)"
" Index Cond: (tank_id = 545)"
" SubPlan 1"
" -> Index Scan using players2_pkey on players2 (cost=0.43..5.45 rows=1 width=10) (actual time=0.105..0.105 rows=1 loops=21963)"
" Index Cond: (account_id = dpg2.account_id)"
"Planning time: 0.212 ms"
"Execution time: 23953.952 ms"
What's really confusing me is that the query planner is trying to resort along the dpg column, when all it needs to do is walk down the index corresponding to the given tank_id and pick the first 50 entries that satisfy the "battles >= 150" condition.
In fact, simply removing the 'order by' clause, I get the same result in 2s because it ends up using the index, which has things sorted in the order I want.
I know I've already solved my problem but why is Postgres doing this and what are the possible solutions?