By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

HOW TO exit from a query

gauravgmbhr
100+
P: 107
hi friends
Well i am trying to create a query which uses a subquery in the from clause
I want as soon as the subquery fetches a row the , the subquery should stop executing , beacuse the table used by subquery has 40 million rows



THIS is so far i have been doing
Expand|Select|Wrap|Line Numbers
  1. SELECT column FROM some_table
  2. WHERE EXISTS  (SELECT * FROM some_other_table WHERE date BETWEEN start_date AND end_date) 
if Any row Exists between start date and end date the main query should execute

But the table in subquery has 40 million rows so i need to stop the whole table scan , and exit the subquery as soon as any row comes from the subquery

I have tried usin IN, SOME, And ANY also
Apr 4 '07 #1
Share this Question
Share on Google+
4 Replies


kershell
P: 11
Try adding 'LIMIT 1' at the end of the subquery.
Apr 4 '07 #2

gauravgmbhr
100+
P: 107
Try adding 'LIMIT 1' at the end of the subquery.

WELL buddy milit is applied after the whole query is evaluated

If u have ever used a postgresql query emplain wizard in PGadmin
u will know that limit is applied after the whole query is evaluated


So limit wont work here
Apr 5 '07 #3

kershell
P: 11
WELL buddy milit is applied after the whole query is evaluated

If u have ever used a postgresql query emplain wizard in PGadmin
u will know that limit is applied after the whole query is evaluated

So limit wont work here
For EXISTS, PostgreSQL documentation clearly says that
The subquery will generally only be executed far enough to determine whether at least one row is returned, not all the way to completion.
So even the LIMIT 1 should not be required. If there is no ORDER BY clause in a query there is no reason for all rows to be accessed.

If i do this

EXPLAIN
SELECT name FROM organisms WHERE EXISTS
(SELECT accession FROM features WHERE id ~ '^SAV' LIMIT 1);
I get
NOTICE: QUERY PLAN:
Result (cost=0.00..20.00 rows=1000 width=32)
InitPlan
-> Limit (cost=0.00..182.71 rows=1 width=32)
-> Seq Scan on features (cost=0.00..27349.21 rows=150 width=32)
-> Seq Scan on organisms (cost=0.00..20.00 rows=1000 width=32)
I interpret it as the limit on the subquery being applied before the main query executes. I could be wrong.
Apr 5 '07 #4

gauravgmbhr
100+
P: 107
For EXISTS, PostgreSQL documentation clearly says that


So even the LIMIT 1 should not be required. If there is no ORDER BY clause in a query there is no reason for all rows to be accessed.

If i do this



I get


I interpret it as the limit on the subquery being applied before the main query executes. I could be wrong.

YA I GOT IT, i read the manual again before ur message, and exists works as i expected, though i asked the qestion becuase of large execution time
Which was not because of the subquery in exists, there was some other mistake that i had been doing


But thanx for ur valuable TIME
regard Gaurav
Apr 10 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.