Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9537A651075 for ; Tue, 1 Jul 2008 08:15:13 -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 83946-07-3 for ; Tue, 1 Jul 2008 08:14:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by postgresql.org (Postfix) with ESMTP id A8CE36517C8 for ; Tue, 1 Jul 2008 07:49:21 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so1049241fgg.23 for ; Tue, 01 Jul 2008 03:49:19 -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:mime-version:content-type; bh=QcpeVx44W6kkX2A3laKzh1iRlSBGLmxPL/mUI7KFBrM=; b=MEfh0wMWWhn+isCQswk3u1Cz1wpSei6zsMNbZTc26QCy3cm1zfqjArt6/fG2XxFnW9 JXFjZK4ZKRsRm/UfDda1nMGjWyezfDp+JN+BzGUVA2taaNRhqkwGx9kTl5FWEbUtcIwh GX+Ho4lqobNqr+k0my6/5UQzp/lmcHIAhmjX4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=oX078LxXEDBQ1CGs3o++ZEsbbbBm2jqcgaNT+VOrk7kfcvX/nvaawloTOo5rdu7UQt iT2l5ydBB+SHYcdssbdlgtwFlLzx6eD9+c+TJaummEu7zzbBITg0Zyf80w+BcggXfJOX JYuY1pIxYLHxKcnjTNkikhz1sqXnLoJ3dGLC8= Received: by 10.86.31.18 with SMTP id e18mr6881908fge.52.1214909359712; Tue, 01 Jul 2008 03:49:19 -0700 (PDT) Received: by 10.86.73.13 with HTTP; Tue, 1 Jul 2008 03:49:19 -0700 (PDT) Message-ID: Date: Tue, 1 Jul 2008 12:49:19 +0200 From: "Emiliano Leporati" To: pgsql-performance@postgresql.org Subject: un-understood index performance behaviour MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12720_6029433.1214909359703" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.098 tagged_above=0 required=5 tests=FB_GET_MEDS=1.097, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200807/1 X-Sequence-Number: 30601 ------=_Part_12720_6029433.1214909359703 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 / ) FROM rtp_frame WHERE i_medium_id = AND i_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 = AND i_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 ? thanks a lot Emiliano ------=_Part_12720_6029433.1214909359703 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ?

thanks a lot

Emiliano
------=_Part_12720_6029433.1214909359703--