Order By Join

Поиск
Список
Период
Сортировка
От Mike Friesen
Тема Order By Join
Дата
Msg-id Pine.LNX.4.30.0102020934480.3649-100000@mike.pmcanada.com
обсуждение исходный текст
Список pgsql-novice
I am trying to retrieve data from several tables in an incremental
fashion.  IE: getting values > 3 limit 60.  I am using the following line:

select icpr.prod_no, icpl.unit_available, iprc.retail where icpr.prod_no >
'04' AND icpr.prod_no=icpl.lprod_no AND icpr.prod_no=iprc.prod_no ORDER BY
icpr.prod_no asc LIMIT 60.

This works very fast.  However if I change the icpr.prod_no > '04' to say
'6' so where icpr.prod_no > '6'.  It is extremely slow.  The explain says:

Merge Join
   -> Merge Join
       -> Index Scan using iprc_prod_no on iprc
       -> Index Scan using icpr_prod_on on icpr
   -> Index Scan using icpl_lprod_no on icpl

However if I change the query to have an upper limit.

select icpr.prod_no, icpl.unit_available, iprc.retail where icpr.prod_no >
'6' AND icpr.prod_no < '7' AND icpr.prod_no=icpl.lprod_no AND
icpr.prod_no=iprc.prod_no ORDER BY icpr.prod_no asc LIMIT 60.

It runs very fast, with the explain saying.
Nested Loop
   -> Nested Loop
    ->Index Scan using icpr_prod_no on icpr
    ->Index Scan using iprc_prod_no on iprc
   -> Index Scan icpl_lprod_no on icpl

My problem comes into that I don't want to hard core an upper limit,
because I really don't know what it could be.  Anyone have an suggestions
on what I can do?

Thanks,

Mike


В списке pgsql-novice по дате отправления:

Предыдущее
От: Herb Pabst
Дата:
Сообщение: php4 mod for apache...
Следующее
От: info@i-developit.com
Дата:
Сообщение: Re: php4 mod for apache...