pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: Jason Coene <jcoene@gotfrag.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Merlin Moncure' <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware upgrade for a high-traffic database
Date: Thu, 12 Aug 2004 08:49:37 +0200
Message-ID: <opsclw8zdccq72hf@musicbox> (raw)
In-Reply-To: <200408120029.i7C0T30E002389@mail.gotfrag.com>
References: <200408120029.i7C0T30E002389@mail.gotfrag.com>

On Wed, 11 Aug 2004 20:29:04 -0400, Jason Coene <jcoene@gotfrag.com> wrote:




> 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)

	Well, you have to read it from the bottom.
	- Index Scan using comments_ix_userid :
	It selects all records for your user.
	rows=407 : there are 407 rows.

	->  Sort  (cost=1608.43..1609.45 rows=407 width=8)
	It sorts them to find the 5 more recent.

	So basically you grab 407 rows to return only 5, so you do 80x more disk  
I/O than necessary. It is likely that posts from all users are interleaved  
in the table, so this probably translates directly into 407 page fetches.

	Note : EXPLAIN ANALYZE will only give good results the first time you run  
it. The second time, all data is in the cache, so it looks really faster  
than it is.

> gf=# EXPLAIN ANALYZE SELECT id FROM comments WHERE userid = 51 ORDER BY
> userid DESC, timestamp DESC LIMIT 5;
> QUERY PLAN
> ----
>  Limit  (cost=0.00..19.90 rows=5 width=12) (actual time=0.040..0.076  
> rows=5
> loops=1)
>    ->  Index Scan Backward using comments_ix_userid_timestamp on comments
> (cost=0.00..1620.25 rows=407 width=12) (actual time=0.035..0.054 rows=5
> loops=1)
>          Index Cond: (userid = 51)
>  Total runtime: 0.134 ms
> (4 rows)
>
> Note: This was done after adding an index on comments (userid, timestamp)

	Well, this one correctly uses the index, fetches 5 rows, and returns them.

	So, excluding index page hits, your unoptimized query has >400 page  
fetches, and your optimized one has 5 page fetches. Still wonder why it's  
faster ?

	Seq scan is fast when locality of reference is good. In your case, it's  
very bad.



view thread (29+ messages)  latest in thread

Message-ID: <opsclw8zdccq72hf@musicbox>
Permalink:  ../opsclw8zdccq72hf@musicbox/
Also on:    postgresql.org/message-id/opsclw8zdccq72hf@musicbox

 · 

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: lists@boutiquenumerique.com, jcoene@gotfrag.com, tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com
  Subject: Re: Hardware upgrade for a high-traffic database
  In-Reply-To: <opsclw8zdccq72hf@musicbox>

* 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