X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4D2EE14B2824 for ; Wed, 11 Aug 2004 23:09:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43278-09 for ; Thu, 12 Aug 2004 02:09:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8138914B27FF for ; Wed, 11 Aug 2004 23:09:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7C29EUa023020; Wed, 11 Aug 2004 22:09:15 -0400 (EDT) To: "Jason Coene" Cc: "'Merlin Moncure'" , pgsql-performance@postgresql.org Subject: Re: Hardware upgrade for a high-traffic database In-reply-to: <200408120029.i7C0T30E002389@mail.gotfrag.com> References: <200408120029.i7C0T30E002389@mail.gotfrag.com> Comments: In-reply-to "Jason Coene" message dated "Wed, 11 Aug 2004 20:29:04 -0400" Date: Wed, 11 Aug 2004 22:09:14 -0400 Message-ID: <23019.1092276554@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/182 X-Sequence-Number: 7818 "Jason Coene" 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