Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 582AA650EA1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  1 Jul 2008 11:18:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 98566-04-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  1 Jul 2008 11:17:55 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29])
	by postgresql.org (Postfix) with ESMTP id E275D650F01
	for <pgsql-performance@postgresql.org>;
	Tue,  1 Jul 2008 11:17:19 -0300 (ADT)
Received: by yx-out-2324.google.com with SMTP id 8so356973yxb.73
	for <pgsql-performance@postgresql.org>;
	Tue, 01 Jul 2008 07:17:18 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=zxoQ0z/7vGoI+RG+7oA4jAsNO0tEMykBYXXtFYgCn+E=;
	b=FpAK56giJXgw3Uvngzdo6jYvJSvk8BJ0ZH5BFYW1yv6tLVZsYEieZUrsVOrqzpwtuE
	aVJB6edzjqNGfoS3g8K2caE35sLpPQgu5CQeNnLJcvSG+eVg1JEEgdl30UZrVPswazaD
	SuTIds/om1dSIQ0/Od5GcleVNQj3bO7a3IeYc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=EmaLAghIidmtjXkEPMbXVIkCS3UdZ9Nbj5gm4Q/6g7w/i2OKiHYsuF9w8tjJDbZxGI
	4ul0feF3v16w+geZ9uyu2kXlxIX5onxPMq/iO9306QcLQK1sqzcTpY1u8zXy3LPUQJkh
	YJHbp9vukG/8Xc03FDDQIBCW7Ph2aso45ufPU=
Received: by 10.143.4.11 with SMTP id g11mr2490408wfi.52.1214921837575;
	Tue, 01 Jul 2008 07:17:17 -0700 (PDT)
Received: by 10.142.153.17 with HTTP; Tue, 1 Jul 2008 07:17:17 -0700 (PDT)
Message-ID: <dcc563d10807010717v7e67dad1y56838f066efac6fb@mail.gmail.com>
Date: Tue, 1 Jul 2008 08:17:17 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Emiliano Leporati" <emiliano.leporati@gmail.com>
Subject: Re: un-understood index performance behaviour
Cc: pgsql-performance@postgresql.org
In-Reply-To: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200807/3
X-Sequence-Number: 30603

On Tue, Jul 1, 2008 at 4:49 AM, Emiliano Leporati
<emiliano.leporati@gmail.com> wrote:
> Hi,
> i have a table with a huge amount of rows (actually 4 millions and a half),
> defined like this:
>
> CREATE TABLE rtp_frame (
>     i_len integer NOT NULL,
>     i_file_offset bigint NOT NULL,
>     i_file_id integer NOT NULL,  -- foreign key
>     i_timestamp bigint NOT NULL,
>     i_loop integer NOT NULL,
>     i_medium_id integer NOT NULL, -- foreign key
>     PRIMARY KEY(i_medium_id, i_loop, i_timestamp)
> );
>
> The primary key creates the btree index.
>
> If I ask the database something like this:
>
> SELECT ((max(i_timestamp) - min(i_timestamp))::double precision / <rate>)
> FROM rtp_frame
> WHERE i_medium_id = <medium> AND i_loop = <loop>;
>
> it replies istantaneously.
>
> But if i ask
>
> DECLARE blablabla INSENSITIVE NO SCROLL CURSOR WITHOUT HOLD FOR
> SELECT i_file_id, i_len, i_file_offset, i_timestamp
> FROM rtp_frame WHERE i_medium_id = <medium>
> AND i_loop = <loop>
> AND i_timestamp BETWEEN 0 and 5400000
> ORDER BY i_timestamp
>
> on a medium with, say, 4 millions rows co-related, it takes 15 seconds to
> reply, even with a different clause on i_timestamp (say i_timestamp >= 0),
> even with the ORDER BY clause specified on the three indexed columns (ORDER
> BY i_medium_id, i_loop, i_timestamp).
>
> Issued on a medium with "just" some hundred thousand rows, it runs
> instantaneously.
>
> If I add a single btree index on i_timestamp, it runs instantaneously event
> on a medium with millions rows (so having a btree(i_medium_id, i_loop,
> i_timestamp) and btree(i_timestamp)).
>
> With (btree(i_medium_id, i_loop) and btree(i_timestamp)), the first for sure
> takes 15 seconds to run, the second i think too but not sure atm.
>
> can anybody explain me why this happens ? and if i should try different
> indexes ?

Not yet, we don't have enough information, although I'm guessing that
the db is switching from an index scan to a sequential scan, perhaps
prematurely.

To see what's happening, run your queries with explain analyze in front...

explain analyze select ...

and see what you get.  Post the output as an attachment here and we'll
see what we can do.