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

Simple JOIN problem

From: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: Simple JOIN problem
Date: 2008-04-28 02:13:32
Message-ID: 481532CC.3080502@dc.baikal.ru (view raw or flat)
Thread:
Lists: pgsql-performance
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);


pgsql-performance by date

Next:From: Dimitri FontaineDate: 2008-04-28 07:49:37
Subject: Re: Best practice to load a huge table from ORACLE to PG
Previous:From: Vlad ArkhipovDate: 2008-04-28 01:51:38
Subject: Re: Optimizer's issue

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