Simple JOIN problem
От | Vlad Arkhipov |
---|---|
Тема | Simple JOIN problem |
Дата | |
Msg-id | 481532CC.3080502@dc.baikal.ru обсуждение исходный текст |
Список | 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 по дате отправления: