I was performing the following query for one of my pages that really
hammered PG:
select b.attacker, b.defender, b.winner, b.n_rounds,
p1.name as attacker_name, p2.name as defender_name,
to_char(b.date_fought, 'HH:MI AM Mon DD YYYY') as tcl_date,
pretty_timestamp(date_fought) as pretty_date
from battles b, parties p1, parties p2
where b.attacker = p1.id
and b.defender = p2.id and 2913 in (b.attacker, b.defender)
order by b.date_fought desc
limit 70
battles is a table containing 300k rows; before the limit, 13k rows
matched the attacker/defender criteria. Both the joins on parties
and the pretty_timestamp function call were really making this slow,
despite the planner using correct indexes.
the following version solves this by doing the limit on a much less
expensive subquery, as follows:
select b.*, pretty_timestamp(date_fought) as pretty_date,
to_char(date_fought, 'HH:MI AM Mon DD YYYY') as tcl_date,
p1.name as attacker_name, p2.name as defender_name
from (select attacker, defender, winner, n_rounds, date_fought
from battles
where 2913 in (attacker, defender)
order by date_fought desc
limit 70) as b, parties p1, parties p2
where b.attacker = p1.id
and b.defender = p2.id
thought this might be useful to someone.
