Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X1W5a-0007og-TC for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 07:33:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X1W5a-00017u-Cb for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 07:33:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X1W5Z-00017h-5s for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 07:33:21 +0000 Received: from mail-la0-f52.google.com ([209.85.215.52]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1X1W5U-0002dl-Lt for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 07:33:19 +0000 Received: by mail-la0-f52.google.com with SMTP id ty20so4461361lab.25 for ; Mon, 30 Jun 2014 00:33:15 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:content-type:mime-version:subject:from :in-reply-to:date:cc:message-id:references:to; bh=REdQ2IuAy4xXleQGOapeuwgU99wyAWOvzNUHtmzs4c0=; b=AHSIuz8QA+AUH4GvSEeXD87dv+ytETen1FQxFQVuLf0M3VN1rRL/hZtJ3j1CQ9/97Y WAQhhPN7KbUAoWFm7N5GRLA1HVJfti2d6zqwsiML7BenUZaGxvQIASqWhZqrnsyd6hLd kDhqYhNfJu7NDxK8djrYyfXKTWS9uxSPIr7x851Pwm/o3xCJjXaEFl5Y2b9JFPv3LXC2 StPIU3NfBdnWIy0Y21wFFC8dNL2cZX/9NQhqxQK1jwrBK3tdFPSswnivaS3+w7XctR6w TW1Ov8dWWwLRW2kgrgrJX93y3KHPBxGQDgWvZaWCUXoxVpt9XQMLmi7ZEpbGEOswOvNS c4fA== X-Gm-Message-State: ALoCoQlGHFIo0RfhZHGqOY6oM6+OJ+gqBmeSMdp04Pg93MpXVXQP44o05Rr0WMHSr5JqfOVO2o9D X-Received: by 10.152.23.233 with SMTP id p9mr29759357laf.31.1404113594918; Mon, 30 Jun 2014 00:33:14 -0700 (PDT) Received: from [192.168.1.22] (0905ds8-ar.0.fullrate.dk. [90.185.23.222]) by mx.google.com with ESMTPSA id p7sm8363329laf.37.2014.06.30.00.33.13 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 30 Jun 2014 00:33:13 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_63F0AEB9-70A4-4837-B989-8182A3BE78D6" Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.2\)) Subject: Re: Guidelines on best indexing strategy for varying searches on 20+ columns From: =?iso-8859-1?Q?Niels_Kristian_Schj=F8dt?= In-Reply-To: Date: Mon, 30 Jun 2014 09:33:10 +0200 Cc: "pgsql-performance@postgresql.org list" Message-Id: References: <79622954-DDEF-4A12-97A0-C67E99D64957@autouncle.com> To: Merlin Moncure X-Mailer: Apple Mail (2.1878.2) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_63F0AEB9-70A4-4837-B989-8182A3BE78D6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Thanks for your suggestions, very useful. See comments inline: Den 25/06/2014 kl. 23.48 skrev Merlin Moncure : > On Wed, Jun 25, 2014 at 3:48 AM, Niels Kristian Schj=F8dt > wrote: >> Hi, >> I=92m running a search engine for cars. It=92s backed by a postgresql = 9.3 installation. >>=20 >> Now I=92m unsure about the best approach/strategy on doing index = optimization for the fronted search. >>=20 >> The problem: >>=20 >> The table containing the cars holds a around 1,5 million rows. People = that searches for cars needs different criteria to search by. Some = search by brand/model, some by year, some by mileage, some by price and = some by special equipment etc. etc. - and often they combine a whole = bunch of criteria together. Of cause some, like brand/mode and price, = are used more frequently than others. In total we offer: 9 category = criteria like brand/model or body type, plus 5 numeric criteria like = price or mileage, plus 12 boolean criteria like equipment. Lastly people = can order the results by different columns (year, price, mileage and a = score we create about the cars). By default we order by our own = generated score. >>=20 >> What I=92ve done so far: >>=20 >> I have analyzed the usage of the criteria =93lightly=94, and created = a few indexes (10). Among those, are e.g. indexes on price, mileage and = a combined index on brand/model. Since we are only interested in showing = results for cars which is actually for sale, the indexes are made as = partial indexes on a sales state column. >>=20 >> Questions: >>=20 >> 1. How would you go about analyzing and determining what columns = should be indexed, and how? >=20 > mainly frequency of access. >=20 >> 2. What is the best strategy when optimizing indexes for searches = happening on 20 + columns, where the use and the combinations varies a = lot? (To just index everything, to index some of the columns, to do = combined indexes, to only do single column indexes etc. etc.) >=20 > don't make 20 indexes. consider installing pg_trgm (for optimized > LIKE searching) or hstore (for optmized key value searching) and then > using GIST/GIN for multiple attribute search. with 9.4 we have > another fancy technique to explore: jsonb searching via GIST/GIN. Interesting, do you have any good resources on this approach? >=20 >> 3. I expect that it does not make sense to index all columns? >=20 > well, maybe. if you only ever search one column at a time, then it > might make sense. but if you need to search arbitrary criteria and > frequently combine a large number, then no -- particularly if your > dataset is very large and individual criteria are not very selective. So, to just clarify: I=92m often combining a large number of search = criteria and the individual criteria is often not very selective, in = that case, are you arguing for or against indexing all columns? :-) >=20 >> 4. I expect it does not make sense to index boolean columns? >=20 > in general, no. an important exception is if you are only interested > in true or false and the number of records that have that interesting > value is tiny relative to the size of the table. in that case, a > partial index can be used for massive optimization. Thanks, hadn=92t been thinking about using partial indexes here as an = option. >=20 >> 5. Is it better to do a combined index on 5 frequently used columns = rather than having individual indexes on each of them? >=20 > Only if you search those 5 columns together a significant portion of = the time. >=20 >> 6. Would it be a goof idea to have all indexes sorted by my default = sorting? >=20 > index order rarely matters. if you always search values backwards and > the table is very large you may want to consider it. unfortunately > this often doesn't work for composite indexes so sometimes we must > explore the old school technique of reversing the value. >=20 >> 7. Do you have so experiences with other approaches that could = greatly improve performance (e.g. forcing indexes to stay in memory = etc.)? >=20 > as noted above, fancy indexing is the first place to look. start > with pg_trgm (for like optmization), hstore, and the new json stuff. > the big limitation you will hit is that that most index strategies, at > least fo the prepackaged stuff will support '=3D', or partial string > (particularly with pg_trgm like), but not > or <: for range operations > you have to post process the search or try to work the index from > another angle. >=20 > merlin --Apple-Mail=_63F0AEB9-70A4-4837-B989-8182A3BE78D6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252 Thanks = for your suggestions, very useful. See comments inline:

Den 25/06/2014 kl. 23.48 skrev Merlin Moncure <mmoncure@gmail.com>:

On Wed, Jun 25, 2014 at 3:48 AM, Niels = Kristian Schj=F8dt
<nielskristian@autouncle.com> wrote:
Hi,
I=92m running a search = engine for cars. It=92s backed by a postgresql 9.3 = installation.

Now I=92m unsure about the best approach/strategy = on doing index optimization for the fronted search.

The = problem:

The table containing the cars holds a around 1,5 million = rows. People that searches for cars needs different criteria to search = by. Some search by brand/model, some by year, some by mileage, some by = price and some by special equipment etc. etc. - and often they combine a = whole bunch of criteria together. Of cause some, like brand/mode and = price, are used more frequently than others. In total we offer: 9 = category criteria like brand/model or body type, plus 5 numeric criteria = like price or mileage, plus 12 boolean criteria like equipment. Lastly = people can order the results by different columns (year, price, mileage = and a score we create about the cars). By default we order by our own = generated score.

What I=92ve done so far:

I have analyzed = the usage of the criteria =93lightly=94, and created a few indexes (10). = Among those, are e.g. indexes on price, mileage and a combined index on = brand/model. Since we are only interested in showing results for cars = which is actually for sale, the indexes are made as partial indexes on a = sales state column.

Questions:

1. How would you go about = analyzing and determining what columns should be indexed, and = how?

mainly frequency of access.

2. What is the best strategy when optimizing indexes for = searches happening on 20 + columns, where the use and the combinations = varies a lot? (To just index everything, to index some of the columns, = to do combined indexes, to only do single column indexes etc. = etc.)

don't make 20 indexes. consider = installing pg_trgm (for optimized
LIKE searching) or hstore (for = optmized key value searching) and then
using GIST/GIN for multiple = attribute search. with 9.4 we have
another fancy technique to = explore: jsonb searching via = GIST/GIN.

Interesting, do you have = any good resources on this approach?

3. I = expect that it does not make sense to index all = columns?

well, maybe. if you only ever search = one column at a time, then it
might make sense. but if you need = to search arbitrary criteria and
frequently combine a large number, = then no -- particularly if your
dataset is very large and individual = criteria are not very = selective.

So, to just clarify: I=92m= often combining a large number of search criteria and the individual = criteria is often not very selective, in that case, are you arguing for = or against indexing all columns? :-)

4. I = expect it does not make sense to index boolean = columns?

in general, no. an important = exception is if you are only interested
in true or false and the = number of records that have that interesting
value is tiny relative = to the size of the table. in that case, a
partial index can be = used for massive = optimization.

Thanks, hadn=92t been = thinking about using partial indexes here as an option.

5. = Is it better to do a combined index on 5 frequently used columns rather = than having individual indexes on each of them?

Only = if you search those 5 columns together a significant portion of the = time.

6. Would it be a goof idea to = have all indexes sorted by my default sorting?

index = order rarely matters. if you always search values backwards = and
the table is very large you may want to consider it. = unfortunately
this often doesn't work for composite indexes so = sometimes we must
explore the old school technique of reversing the = value.

7. Do you have so experiences = with other approaches that could greatly improve performance (e.g. = forcing indexes to stay in memory etc.)?

as noted = above, fancy indexing is the first place to look. = start
with pg_trgm (for like optmization), hstore, and = the new json stuff.
the big limitation you will hit is that that most = index strategies, at
least fo the prepackaged stuff will support '=3D',= or partial string
(particularly with pg_trgm like), but not > or = <: for range operations
you have to post process the search or try = to work the index from
another = angle.

merlin

= --Apple-Mail=_63F0AEB9-70A4-4837-B989-8182A3BE78D6--