DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=j+uwm+5rlHrgw5vh8E6jn5QQvmAEGZjn4jM5hisqDxzbdz1kzOfksKX2I4CEoAcJMpeGs3pRKR9ZYIqaCO7iAmRFFMBTyFu0jkx8PGoJMi6A8Vsz/wfwaMYT8mRt5NpfuwwhgZq8FSuXyON1kcW+lpgQpy4H9awqOGlVkhznDJ0=
Message-ID: <46B77ACA.9010302@enterprisedb.com>
Date: Mon, 06 Aug 2007 20:47:22 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Henrik Zagerholm <henke@mac.se>
CC: Tom Lane <tgl@sss.pgh.pa.us>,  pgsql-performance@postgresql.org
Subject: Re: Extreme slow select query 8.2.4
References: <B1D089D3-5CB8-45F7-93EA-A81A492A543E@mac.se>
	<27459.1186412327@sss.pgh.pa.us>
	<3176138F-E316-46AF-99AE-0CC8F1EF1E73@mac.se>
In-Reply-To: <3176138F-E316-46AF-99AE-0CC8F1EF1E73@mac.se>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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