One idea I'm thinking of my self is that I have a column called state on the adverts which can either be 'active' or 'deactivated'. The absolute amount of 'active adverts are relatively constant (currently 15%) where the remaining and growing part is 'deactivated'.
You might consider deleting the rows from the active table, rather than just setting an inactive flag, possibly inserting them into a history table, if you need to preserve the info. You can do that in a single statement using "WITH foo as (delete from advert where ... returning *) insert into advert_history select * from foo"
In reality the adverts that are selected is all 'active'. I'm hence wondering if it theoretically (and in reality of cause) would make my query faster if I did something like: "SELECT .* FROM cars LEFT OUTER JOIN adverts on cars.id = adverts.car_id WHERE cars.brand = 'Audi' AND adverts.state = 'active'" with a partial index on "INDEX adverts ON (car_id) WHERE state = 'active'"?
The left join isn't doing you much good there, as the made-up rows just get filtered out anyway.
The partial index could help, but not as much as partitioning away the inactive records from the table, as well as from the index.
