Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 154BF2E004E
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  6 May 2008 08:19:46 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 32108-07-2
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  6 May 2008 08:19:25 -0300 (ADT)
Received: from mx1.hub.org (mx1.hub.org [200.46.208.106])
	by developer.postgresql.org (Postfix) with ESMTP id 948992E0068
	for <pgsql-performance@postgresql.org>;
	Tue,  6 May 2008 08:19:19 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from dc.baikal.ru (dc.baikal.ru [212.176.3.237])
	by mx1.hub.org (Postfix) with ESMTP id 734BDB077B0
	for <pgsql-performance@postgresql.org>;
	Tue,  6 May 2008 08:19:14 -0300 (ADT)
Received: from it319k5.dc-local (it319k5.dc-local [10.0.4.75])
	(authenticated bits=0)
	by dc.baikal.ru (8.13.8/8.13.8) with ESMTP id m46BE3vX006100
	for <pgsql-performance@postgresql.org>; Tue, 6 May 2008 20:14:04 +0900
Message-ID: <48203D7B.2000809@dc.baikal.ru>
Date: Tue, 06 May 2008 20:14:03 +0900
From: Vlad Arkhipov <arhipov@dc.baikal.ru>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Seqscan problem
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: ClamAV version 0.93,
	clamav-milter version 0.93 on dc.baikal.ru
X-Virus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000
X-Spam-Level: 
X-Archive-Number: 200805/74
X-Sequence-Number: 30042

I've just discovered a problem with quite simple query. It's really 
confusing me.
Postgresql 8.3.1, random_page_cost=1.1. All tables were analyzed before 
query.

EXPLAIN ANALYZE
SELECT i.c, d.r
FROM i
  JOIN d ON d.cr = i.c
WHERE i.dd between '2007-08-01' and '2007-08-30'

Hash Join  (cost=2505.42..75200.16 rows=98275 width=16) (actual 
time=2728.959..23118.632 rows=93159 loops=1)
  Hash Cond: (d.c = i.c)
  ->  Seq Scan on d d  (cost=0.00..61778.75 rows=5081098 width=16) 
(actual time=0.075..8859.807 rows=5081098 loops=1)
  ->  Hash  (cost=2226.85..2226.85 rows=89862 width=8) (actual 
time=416.526..416.526 rows=89473 loops=1)
        ->  Index Scan using i_dd on i  (cost=0.00..2226.85 rows=89862 
width=8) (actual time=0.078..237.504 rows=89473 loops=1)
              Index Cond: ((dd >= '2007-08-01'::date) AND (dd <= 
'2007-08-30'::date))
Total runtime: 23246.640 ms

EXPLAIN ANALYZE
SELECT i.*, d.r
FROM i
  JOIN d ON d.c = i.c
WHERE i.dd between '2007-08-01' and '2007-08-30'

Nested Loop  (cost=0.00..114081.69 rows=98275 width=416) (actual 
time=0.114..1711.256 rows=93159 loops=1)
  ->  Index Scan using i_dd on i  (cost=0.00..2226.85 rows=89862 
width=408) (actual time=0.075..207.574 rows=89473 loops=1)
        Index Cond: ((dd >= '2007-08-01'::date) AND (dd <= 
'2007-08-30'::date))
  ->  Index Scan using d_uniq on d  (cost=0.00..1.24 rows=2 width=16) 
(actual time=0.007..0.009 rows=1 loops=89473)
        Index Cond: (d.c = i.c)
Total runtime: 1839.228 ms

And this never happened with LEFT JOIN.

EXPLAIN ANALYZE
SELECT i.c, d.r
FROM i
  LEFT JOIN d ON d.cr = i.c
WHERE i.dd between '2007-08-01' and '2007-08-30'

Nested Loop Left Join  (cost=0.00..114081.69 rows=98275 width=16) 
(actual time=0.111..1592.225 rows=93159 loops=1)
  ->  Index Scan using i_dd on i  (cost=0.00..2226.85 rows=89862 
width=8) (actual time=0.072..210.421 rows=89473 loops=1)
        Index Cond: ((dd >= '2007-08-01'::date) AND (dd <= 
'2007-08-30'::date))
  ->  Index Scan using d_uniq on d  (cost=0.00..1.24 rows=2 width=16) 
(actual time=0.007..0.009 rows=1 loops=89473)
        Index Cond: (d.c = i.c)
"Total runtime: 1720.185 ms"

d_uniq is unique index on d(r, ...).