Why does postgres seach in ALL lines (not optimal!)

From: markus(dot)cl(at)gmx(dot)de (Markus Dehmann)
To: pgsql-general(at)postgresql(dot)org
Subject: Why does postgres seach in ALL lines (not optimal!)
Date: 2002-10-24 22:32:01
Message-ID: c1e48b51.0210241432.11ec2ed9@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have two tables with entries that have the same IDs. My select shall
get all entries from both tables that have an ID > 19000, all in all I
have 19577 entries.

select * from m, t where m.id > 19000 and t.messageid = m.id;

Postgres should immediately get the 577 lines from m, and get the
corresponding 577 lines from table t, using the primary keys. But, it
scans ALL the lines in table t which seems highly inefficient:

db=# EXPLAIN select * from m, t
db-# where m.id > 19000 and t.messageid = m.id;

Merge Join (cost=0.00..628.96 rows=550 width=172)
-> Index Scan using pk_m on m (cost=0.00..17.43 rows= 550
width=101)
-> Index Scan using pk_t on t (cost=0.00..554.34 rows=19576 (!!!)
width=71)

Why is this and how can I make postgres search fewer rows in the table
t?
Any help is very appreciated!!!
Markus

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2002-10-24 22:40:09 Re: Hot Backup
Previous Message Owen Jacobson 2002-10-24 21:36:52 Automatic insertion on insertion on another table