HOW TO exit from a query

[gauravgmbhr]

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

SELECT column FROM some_table
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

[kershell]

Try adding 'LIMIT 1' at the end of the subquery.

[gauravgmbhr]

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

[kershell]

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..273 49.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.

[gauravgmbhr]

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