Re: Why is query selecting sequential? - Mailing list pgsql-performance
From | Karl Denninger |
---|---|
Subject | Re: Why is query selecting sequential? |
Date | |
Msg-id | 20040206162232.D4910@Denninger.Net Whole thread Raw |
In response to | Re: Why is query selecting sequential? (Josh Berkus <josh@agliodbs.com>) |
Responses |
Re: Why is query selecting sequential?
Re: Why is query selecting sequential? |
List | pgsql-performance |
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 bypinned 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!
pgsql-performance by date: