We've got a rather large table and a situation where ours users might do a
query that essentially says "give me everything", or give me 20,000 rows...
We want to limit this so that a reasonable number of hits are returned in a
reasonable length of time.
We're using "LIMIT" to do this, but it appears that Postgres is going out &
retrieving everything first and THEN applying the limit. (An EXPLAIN of the
query with & without LIMIT bears this theory out...)
So my question is, are we using LIMIT in the wrong way, or is there a better
way to achieve our purpose?
We're using JDBC to submit the query via Java, and The query looks like
this:
select
court_config.court_location_text,actor_case_assignment.case_id,actor_person_
date_of_birth,assigned_case_role,actor_case_assignment.court_ori,actor.actor
_full_name,actor_case_assignment.actor_id,case_data.local_type_code,case_dat
a.local_subtype_code,actor_case_assignment.impound_litigant_data,actor.actor
_alias_for_actor_id from court_config,actor_case_assignment,actor,case_data
where ( court_config.court_ori like 'IL' or
court_config.court_address_state like 'IL' ) and court_config.court_ori =
actor_case_assignment.court_ori and (actor.actor_id =
actor_case_assignment.actor_id or actor.actor_alias_for_actor_id =
actor_case_assignment.actor_id) and court_config.court_ori =
actor_case_assignment.court_ori and case_data.case_id =
actor_case_assignment.case_id order by case_id limit 200,2000;
TIA for any help! -I'll try to return the favor some day!
-Nick
---------------------------------------------------------------------
Nick Fankhauser
Business:
nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
Personal:
nickf@fankhausers.com http://www.fankhausers.com
