pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jason Coene <jcoene@gotfrag.com>
Cc: 'Merlin Moncure' <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware upgrade for a high-traffic database
Date: Wed, 11 Aug 2004 22:09:14 -0400
Message-ID: <23019.1092276554@sss.pgh.pa.us> (raw)
In-Reply-To: <200408120029.i7C0T30E002389@mail.gotfrag.com>
References: <200408120029.i7C0T30E002389@mail.gotfrag.com>

"Jason Coene" <jcoene@gotfrag.com> writes:
> We have a lot of:

> SELECT whatever
> 	FROM ourtable
> 	WHERE field1 = X
> 	AND field2 = Y
> 	AND field3 = Z
> 	ORDER BY id DESC
> 	LIMIT 5

> With indexes:

> ourtable(id)
> ourtable(field1, field2, field3)

> Is it standard procedure with postgres to include any fields listed in WHERE
> in the ORDER BY, and create a single index for only the ORDER BY fields (in
> order of appearance, of course)?

It depends.  If the X/Y/Z constraint is already pretty selective, then
it seems sufficient to me to pick up the matching rows (using the
3-field index), sort them by id, and take the first 5.  The case where
the extra-index-column trick is useful is where the WHERE clause *isn't*
real selective and so a lot of rows would have to be sorted.  In your
previous example, I imagine you have a lot of prolific posters and so
"all posts by userid 42" can be a nontrivial set.  The double-column
index lets you skip the sort and just pull out the required rows by
scanning from the end of the range of userid = 42 entries.

> gf=# EXPLAIN ANALYZE SELECT id FROM comments WHERE userid = 51 ORDER BY
> timestamp DESC LIMIT 5;
>                                                                   QUERY PLAN
> ----------------------------------------------------------------------------
> -------------------------------------------------------------------
>  Limit  (cost=1608.43..1608.45 rows=5 width=8) (actual time=0.292..0.317
> rows=5 loops=1)
>    ->  Sort  (cost=1608.43..1609.45 rows=407 width=8) (actual
> time=0.287..0.295 rows=5 loops=1)
>          Sort Key: "timestamp"
>          ->  Index Scan using comments_ix_userid on comments
> (cost=0.00..1590.79 rows=407 width=8) (actual time=0.031..0.190 rows=35
> loops=1)
>                Index Cond: (userid = 51)
>  Total runtime: 0.375 ms
> (6 rows)

This example looks fine, but since userid 51 evidently only has 35
posts, there's not much time needed to read 'em all and sort 'em.  The
place where the double-column index will win big is on userids with
hundreds of posts.

You have to keep in mind that each index costs time to maintain during
inserts/updates.  So adding an index just because it makes a few queries
a little faster probably isn't a win.  You need to make tradeoffs.

			regards, tom lane



view thread (29+ messages)  latest in thread

Message-ID: <23019.1092276554@sss.pgh.pa.us>
Permalink:  ../23019.1092276554@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23019.1092276554@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jcoene@gotfrag.com, merlin.moncure@rcsonline.com
  Subject: Re: Hardware upgrade for a high-traffic database
  In-Reply-To: <23019.1092276554@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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