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

On 24 Oct 2002, Markus Dehmann wrote:

> 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?

Well, it seems to want to do a merge join which doesn't seem like a bad
idea.  It doesn't imply t.messageid>19000 from the two clauses above.  You
could probably add it which might make a happier seeming plan, although
I'm not sure it'd help much in actual execution.  Without
t.messageid>19000 it's basically using the index scan on t to get the rows
in sorted order to do the merge.