Re: Advice on optimizing select/index

On Wed, May 22, 2013 at 7:37 AM, Niels Kristian Schjødt <
nielskristian(at)autouncle(dot)com> wrote:

>
> 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.

Cheers,

Jeff