Обсуждение: Indexes and outer join?

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

Heni Lolov <hal_bg@yahoo.com> 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