pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karl Denninger <karl@denninger.net>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Why is query selecting sequential?
Date: Sat, 07 Feb 2004 01:51:54 -0500
Message-ID: <17425.1076136714@sss.pgh.pa.us> (raw)
In-Reply-To: <20040206162232.D4910@Denninger.Net>
References: <20040206153602.B4910@Denninger.Net>
	<200402061351.39084.josh@agliodbs.com>
	<20040206162232.D4910@Denninger.Net>

Karl Denninger <karl@denninger.net> writes:
> akcs=> explain analyze select forum, (replied > (select lastview from forumlog where forumlog.login='genesis' and forumlog.forum='General' and number=post.number)) as newflag, * from post where forum = 'General' and toppost = 1 order by pinned desc, replied desc;               
>                                                          QUERY PLAN                                                         
> ----------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=28.41..28.42 rows=6 width=218) (actual time=0.677..0.698 rows=5 loops=1)
>    Sort Key: pinned, replied
>    ->  Index Scan using post_toppost on post  (cost=0.00..28.33 rows=6 width=218) (actual time=0.403..0.606 rows=5 loops=1)
>          Index Cond: ((forum = 'General'::text) AND (toppost = 1))
>          SubPlan
>            ->  Seq Scan on forumlog  (cost=0.00..1.18 rows=1 width=8) (actual time=0.015..0.027 rows=1 loops=5)
>                  Filter: ((login = 'genesis'::text) AND (forum = 'General'::text) AND (number = $0))
>  Total runtime: 0.915 ms
> (8 rows)

As noted elsewhere, the inner subplan will not switch over to an
indexscan until you get some more data in that table.  Note however that
the subplan is only accounting for about 0.13 msec (0.027*5) so it's not
the major cost here anyway.  The slow part seems to be the indexed fetch
from "post", which is taking nearly 0.5 msec to fetch five rows.

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <17425.1076136714@sss.pgh.pa.us>
Permalink:  ../17425.1076136714@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17425.1076136714@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, karl@denninger.net, josh@agliodbs.com
  Subject: Re: Why is query selecting sequential?
  In-Reply-To: <17425.1076136714@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