Skip site navigation (1) Skip section navigation (2)

Seq scan on join, not on subselect? analyze this

From: Bryce Nesbitt <bryce2(at)obviously(dot)com>
To: sql pgsql <pgsql-sql(at)postgresql(dot)org>
Subject: Seq scan on join, not on subselect? analyze this
Date: 2008-11-02 17:51:21
Message-ID: 490DE899.8000805@obviously.com (view raw or flat)
Thread:
Lists: pgsql-sql
I'm a bit confused why the query planner is not restricting my join, and
not using the index.  Two explain analyze statements follow.
Why is the second so much better?

lyell5=> select version();
PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)

lyell5=> explain analyze select * from article_words join words using
(word_key) where context_key=535462;
+------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                  
QUERY
PLAN                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Hash Join  (cost=192092.90..276920.93 rows=45327 width=17) (actual
time=6020.932..60084.817 rows=777
loops=1)                                  |
|   Hash Cond: (article_words.word_key =
words.word_key)                                                                                        
|
|   ->  Index Scan using article_word_idx on article_words 
(cost=0.00..55960.50 rows=45327 width=8) (actual time=0.031..0.547
rows=777 loops=1) |
|         Index Cond: (context_key =
535462)                                                                                                    
|
|   ->  Hash  (cost=93819.62..93819.62 rows=5653462 width=13) (actual
time=6020.605..6020.605 rows=5651551 loops=1)                              |
|         ->  Seq Scan on words  (cost=0.00..93819.62 rows=5653462
width=13) (actual time=0.006..2010.962 rows=5651551
loops=1)                  |
| Total runtime: 60085.616
ms                                                                                                                   
|
+------------------------------------------------------------------------------------------------------------------------------------------------+


lyell5=> explain analyze select * from words where word_key in (select
word_key from article_words where context_key=535462);
+------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                     
QUERY
PLAN                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=56073.81..56091.41 rows=2 width=13) (actual
time=0.808..4.723 rows=777
loops=1)                                                   |
|   ->  HashAggregate  (cost=56073.81..56073.83 rows=2 width=4) (actual
time=0.795..1.072 rows=777
loops=1)                                            |
|         ->  Index Scan using article_word_idx on article_words 
(cost=0.00..55960.50 rows=45327 width=4) (actual time=0.030..0.344
rows=777 loops=1) |
|               Index Cond: (context_key =
535462)                                                                                                    
|
|   ->  Index Scan using words_pkey on words  (cost=0.00..8.78 rows=1
width=13) (actual time=0.003..0.004 rows=1
loops=777)                            |
|         Index Cond: (words.word_key =
article_words.word_key)                                                                                       
|
| Total runtime: 4.936
ms                                                                                                                             
|
+------------------------------------------------------------------------------------------------------------------------------------------------------+


Responses

pgsql-sql by date

Next:From: Bryce NesbittDate: 2008-11-02 18:04:01
Subject: Seq scan on join, not on subselect? analyze this
Previous:From: Gregory StarkDate: 2008-10-31 10:58:40
Subject: Re: Subqueries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group