From: =?iso-8859-1?Q?Niels_Kristian_Schj=F8dt?= <nielskristian@autouncle.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Advice on optimizing select/index
Message-Id: <39DB3954-1DDF-4551-B794-E65AF48F2C96@autouncle.com>
Date: Wed, 22 May 2013 16:37:36 +0200
To: pgsql-performance@postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 6.3 \(1503\))
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 pr=
etty 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 a=
dverts on cars.id =3D adverts.car_id WHERE cars.brand =3D 'Audi'" is too po=
or. I have identified that it's the Adverts table part that performs very b=
ad, and it's by far the biggest of the two. I would like to optimize the qu=
ery/index, but I don't know if there at all is any theoretical option of ac=
tually getting a performance boost on a join, where the foreign key is alre=
ady indexed?

One idea I'm thinking of my self is that I have a column called state on th=
e adverts which can either be 'active' or 'deactivated'. The absolute amoun=
t of 'active adverts are relatively constant (currently 15%) where the rema=
ining and growing part is 'deactivated'.

In reality the adverts that are selected is all 'active'. I'm hence wonderi=
ng 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 =3D adverts.car_id WHERE cars.brand =3D 'Audi' AND adverts.state =
=3D 'active'" with a partial index on "INDEX adverts ON (car_id) WHERE stat=
e =3D 'active'"?

Regards Niels Kristian

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance