From: Vlad Arkhipov <arhipov@dc.baikal.ru>
To: pgsql-performance@postgresql.org
Subject: Simple JOIN problem
Date: Mon, 28 Apr 2008 11:13:32 +0900
Message-ID: <481532CC.3080502@dc.baikal.ru> (raw)
I run on PostgreSQL 8.3, default settings (also tried to change
random_page_cost close to 1).
What need I change to make the second query run as fast as the first?
Set enable_hashjoin to off solves this problem, but it's not the way I
can use.
Statistics for all columns is on the level 1000.
explain analyze
select *
from c
join i on i.c_id = c.id
where c.d between '2007-02-01' and '2007-02-06'
Nested Loop (cost=0.00..25066.24 rows=4771 width=28) (actual
time=0.129..52.499 rows=5215 loops=1)
-> Index Scan using c_d_idx on c (cost=0.00..86.77 rows=2368
width=12) (actual time=0.091..4.623 rows=2455 loops=1)
Index Cond: ((d >= '2007-02-01'::date) AND (d <=
'2007-02-06'::date))
-> Index Scan using i_c_id_idx on i (cost=0.00..10.51 rows=3
width=16) (actual time=0.006..0.010 rows=2 loops=2455)
Index Cond: (i.c_id = c.id)
Total runtime: 59.501 ms
explain analyze
select *
from c
join i on i.c_id = c.id
where c.d between '2007-02-01' and '2007-02-07'
Hash Join (cost=143.53..27980.95 rows=6021 width=28) (actual
time=612.282..4162.321 rows=6497 loops=1)
Hash Cond: (i.c_id = c.id)
-> Seq Scan on i (cost=0.00..19760.59 rows=1282659 width=16) (actual
time=0.073..2043.658 rows=1282659 loops=1)
-> Hash (cost=106.18..106.18 rows=2988 width=12) (actual
time=11.635..11.635 rows=3064 loops=1)
-> Index Scan using c_d_idx on c (cost=0.00..106.18 rows=2988
width=12) (actual time=0.100..6.055 rows=3064 loops=1)
Index Cond: ((d >= '2007-02-01'::date) AND (d <=
'2007-02-07'::date))
Total runtime: 4171.049 ms
CREATE TABLE c
(
id bigint NOT NULL,
d date,
CONSTRAINT c_id_pk PRIMARY KEY (id)
);
CREATE INDEX c_d_idx
ON c
USING btree
(d);
CREATE TABLE i
(
val bigint,
c_id bigint,
CONSTRAINT i_c_id_fk FOREIGN KEY (c_id)
REFERENCES c (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE INDEX i_c_id_idx
ON i
USING btree
(c_id);
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: arhipov@dc.baikal.ru
Subject: Re: Simple JOIN problem
In-Reply-To: <481532CC.3080502@dc.baikal.ru>
* 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