pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAdvice on optimizing select/index
3+ messages / 3 participants
[nested] [flat]
* Advice on optimizing select/index
@ 2013-05-22 14:37 Niels Kristian Schjødt <nielskristian@autouncle.com>
0 siblings, 2 replies; 3+ messages in thread
From: Niels Kristian Schjødt @ 2013-05-22 14:37 UTC (permalink / raw)
To: pgsql-performance
Hi, I have a database where one of my tables (Adverts) are requested a LOT. It's a relatively narrow table with 12 columns, but the size is growing pretty rapidly. The table is used i relation to another one called (Car), and in the form of "cars has many adverts". I have indexed the foreign key car_id on Adverts.
However the performance when doing a "SELECT .* FROM cars LEFT OUTER JOIN adverts on cars.id = adverts.car_id WHERE cars.brand = 'Audi'" is too poor. I have identified that it's the Adverts table part that performs very bad, and it's by far the biggest of the two. I would like to optimize the query/index, but I don't know if there at all is any theoretical option of actually getting a performance boost on a join, where the foreign key is already indexed?
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'.
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'"?
Regards Niels Kristian
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Advice on optimizing select/index
@ 2013-05-26 12:34 Robert Klemme <shortcutter@googlemail.com>
parent: Niels Kristian Schjødt <nielskristian@autouncle.com>
1 sibling, 0 replies; 3+ messages in thread
From: Robert Klemme @ 2013-05-26 12:34 UTC (permalink / raw)
To: pgsql-performance
On 22.05.2013 16:37, Niels Kristian Schjødt wrote:
> 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'"?
That sounds reasonable to do. If you have enough bandwidth on your
production database why not just try it out? Otherwise you could do
this on a test database and see how it goes and what plan you get. Btw.
did you look at the original plan?
Cheers
robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Advice on optimizing select/index
@ 2013-06-03 16:49 Jeff Janes <jeff.janes@gmail.com>
parent: Niels Kristian Schjødt <nielskristian@autouncle.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jeff Janes @ 2013-06-03 16:49 UTC (permalink / raw)
To: Niels Kristian Schjødt <nielskristian@autouncle.com>; +Cc: pgsql-performance
On Wed, May 22, 2013 at 7:37 AM, Niels Kristian Schjødt <
nielskristian@autouncle.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-06-03 16:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-22 14:37 Advice on optimizing select/index Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-05-26 12:34 ` Robert Klemme <shortcutter@googlemail.com>
2013-06-03 16:49 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox