Re: Why is query selecting sequential?

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(at)denninger(dot)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!