From: Emiliano Leporati <emiliano.leporati@gmail.com>
To: pgsql-performance@postgresql.org
Subject: un-understood index performance behaviour
Date: Tue, 1 Jul 2008 12:49:19 +0200
Message-ID: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.com> (raw)
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
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: emiliano.leporati@gmail.com
Subject: Re: un-understood index performance behaviour
In-Reply-To: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.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