Indexes and outer join?

Поиск
Список
Период
Сортировка
Искать

Indexes and outer join?

От:
Heni Lolov <hal_bg@yahoo.com>
Дата:
Hi!
I have the the folowing objects:

CREATE TABLE sao(
	id INT4,
	type VARCHAR,
	ra  INT4,
	de INT4,
	mag INT2
);

create index sao_drm on sao(de,ra,mag);
create index sao_dm on sao(de,mag);
create index sao_i on sao(id);

CREATE TABLE tycho(
	reg_id INT2,
	id INT2,
	ci INT2,
	ra  INT4,
	de INT4,
	mag INT2,
	hip INT4,
	ppm INT4,
	hd INT4,
	bd VARCHAR
);

create index tycho_drm on tycho(de,ra,mag);
create index tycho_dm on tycho(de,mag);
create index tycho_iic on tycho(reg_id,id,ci);
create index tycho_p on tycho(ppm);
create index tycho_h on tycho(hd);
create index tycho_b on tycho(bd);

CREATE VIEW tyc_glup AS 
	SELECT sao.id,sao.type,tycho.ra,tycho.de,tycho.mag 
	FROM tycho LEFT OUTER JOIN sao 
	ON (tycho.ppm=sao.id);

=================================
explain select * 
from tyc_glup 
where de>0 and de<1000000 and ra<100000 and ra>0;

NOTICE:  QUERY PLAN:

Merge Join  (cost=42975.66..44050.21 rows=34159 width=50)
  ->  Sort  (cost=108.53..108.53 rows=26 width=14)
        ->  Index Scan using tycho_drm on tycho  (cost=0.00..107.90 rows=26
width=14)
  ->  Sort  (cost=42867.14..42867.14 rows=258997 width=36)
        ->  Seq Scan on sao  (cost=0.00..4609.97 rows=258997 width=36)

EXPLAIN
=================================
explain select sao.id,sao.type,tycho.ra,tycho.de,tycho.mag from tycho,sao where
tycho.ppm=sao.id;
NOTICE:  QUERY PLAN:

Merge Join  (cost=238805.10..17321566.61 rows=1366358098 width=50)
  ->  Sort  (cost=195937.97..195937.97 rows=1055115 width=14)
        ->  Seq Scan on tycho  (cost=0.00..19099.15 rows=1055115 width=14)
  ->  Sort  (cost=42867.14..42867.14 rows=258997 width=36)
        ->  Seq Scan on sao  (cost=0.00..4609.97 rows=258997 width=36)

EXPLAIN
==================================

So is there any way to force sao_i index use on sao table and how? The tables
are large and the query takes too much time. 

Thanks in advance!

Rumen





__________________________________________________
Do You Yahoo!?
Yahoo! Sports - sign up for Fantasy Baseball
http://sports.yahoo.com

Re: Indexes and outer join?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Heni Lolov  writes:
> explain select * 
> from tyc_glup 
> where de>0 and de<1000000 and ra<100000 and ra>0;

> NOTICE:  QUERY PLAN:

> Merge Join  (cost=42975.66..44050.21 rows=34159 width=50)
>   ->  Sort  (cost=108.53..108.53 rows=26 width=14)
>         ->  Index Scan using tycho_drm on tycho  (cost=0.00..107.90 rows=26
> width=14)
>   ->  Sort  (cost=42867.14..42867.14 rows=258997 width=36)
>         ->  Seq Scan on sao  (cost=0.00..4609.97 rows=258997 width=36)

Hmm.  This plan doesn't make a lot of sense to me; with so few rows
coming out of tycho, I'd have expected the thing to pick a nestloop.
Do you have enable_nestloop turned off?  What plan do you get for this
query if you set enable_mergejoin to off?

Also, what PG version is this?

			regards, tom lane
FAQ