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 по дате отправления: