I have the following table:
CREATE TABLE dpg2 ( account_id integer NOT NULL, tank_id integer NOT NULL, battles integer, dmg integer, frags integer, wins integer, recent_battles integer, recent_dmg integer, recent_frags integer, recent_wins integer, dpg real, recent_ts timestamp with time zone, recent_dpg real, CONSTRAINT dpg2_pkey PRIMARY KEY (account_id, tank_id) )
with this index:
CREATE INDEX dpg_tank_id_idx ON dpg2 USING btree (tank_id, dpg DESC NULLS LAST);
I ran the following query:
explain analyze 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 from dpg2 where tank_id=545 and battles >= 150 order by dpg desc limit 50;
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?
Edit: I already ran analyze on the table and autovacuum is on.