From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Henrik Zagerholm <henke@mac.se>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Extreme slow select query 8.2.4
Date: Mon, 06 Aug 2007 20:47:22 +0100
Message-ID: <46B77ACA.9010302@enterprisedb.com> (raw)
In-Reply-To: <3176138F-E316-46AF-99AE-0CC8F1EF1E73@mac.se>
References: <B1D089D3-5CB8-45F7-93EA-A81A492A543E@mac.se>
<27459.1186412327@sss.pgh.pa.us>
<3176138F-E316-46AF-99AE-0CC8F1EF1E73@mac.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, henke@mac.se, tgl@sss.pgh.pa.us
Subject: Re: Extreme slow select query 8.2.4
In-Reply-To: <46B77ACA.9010302@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox