pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Karl Denninger <karl@denninger.net>
To: josh@agliodbs.com
To: pgsql-performance@postgresql.org
Subject: Re: Why is query selecting sequential?
Date: Fri, 6 Feb 2004 16:22:32 -0600
Message-ID: <20040206162232.D4910@Denninger.Net> (raw)
In-Reply-To: <200402061351.39084.josh@agliodbs.com>
References: <20040206153602.B4910@Denninger.Net>
	<200402061351.39084.josh@agliodbs.com>

On Fri, Feb 06, 2004 at 01:51:39PM -0800, Josh Berkus wrote:
> Karl,
> 
> >          SubPlan
> >            ->  Seq Scan on forumlog  (cost=0.00..1.18 rows=1 width=8)
> >                  Filter: ((login = '%s'::text) AND (forum = '%s'::text) AND 
> (number = $0))
> 
> > Why is the subplan using a sequential scan?  At minimum the index on the 
> > post number ("forumlog_number") should be used, no?  What would be even
> > better would be a set of indices that allow at least two (or even all three)
> > of the keys in the inside SELECT to be used.
> 
> It's using a seq scan because you have only 1 row in the table.     Don't 
> bother testing performance before your database is populated.
> 
> PostgreSQL doesn't just use an index because it's there; it uses and index 
> because it's faster than not using one.
> 
> If there is more than one row in the table, then:
> 1) run ANALYZE forumlog;
> 2) Send us the EXPLAIN ANALYZE, not just the explain for the query.

Hmmm... there is more than one row in the table. :-)  There aren't a huge
number, but there are a few.  I know about the optimizer not using indices 
if there are no (or only one) row in the table - not making that
mistake here.

Ran analyze forumlog;

Same results.

Here's an explain analyze with actual values (that DO match real values in
the table) filled in.

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)

--
-- 
Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist
http://www.denninger.net	Tired of spam at your company?  LOOK HERE!
http://childrens-justice.org	Working for family and children's rights
http://diversunion.org		LOG IN AND GET YOUR TANK STICKERS TODAY!



view thread (8+ messages)  latest in thread

Message-ID: <20040206162232.D4910@Denninger.Net>
Permalink:  ../20040206162232.D4910@Denninger.Net/
Also on:    postgresql.org/message-id/20040206162232.D4910@Denninger.Net

 · 

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: karl@denninger.net, josh@agliodbs.com
  Subject: Re: Why is query selecting sequential?
  In-Reply-To: <20040206162232.D4910@Denninger.Net>

* 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