Skip site navigation (1) Skip section navigation (2)

Order By Join

From: Mike Friesen <mfriesen(at)pmcanada(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Order By Join
Date: 2001-02-02 17:18:11
Message-ID: Pine.LNX.4.30.0102020934480.3649-100000@mike.pmcanada.com (view raw or flat)
Thread:
Lists: 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 by date

Next:From: infoDate: 2001-02-02 17:29:29
Subject: Re: php4 mod for apache...
Previous:From: Herb PabstDate: 2001-02-02 17:03:08
Subject: php4 mod for apache...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group