Re: Extreme slow select query 8.2.4

Henrik Zagerholm wrote:
> I know the query retrieves way more which is really necessary to show to
> the user so I would gladly come up with a way to limit the query so the
> GUI doesn't hang for several minutes if a user does a bad search.
> The problem is that I don't know a good way of limit the search
> efficiently as only going on tbl_file with limit 100 could make the
> query only to return 10 rows if the user doesn't have access to 900 of
> the files (This is what the join with tbl_acl does). Using cursors
> doesn't help because I really don't retrieve that much data

Could you just add a LIMIT 100 to the end of the query, if 100 rows is
enough? That would cut the runtime of the query, if there's a quicker
plan to retrieve just those 100 rows.

Another alternative is to use statement_timeout. If a query takes longer
than specified timeout, it's automatically aborted and an error is given.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com