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