From: Scott Marlowe <scott.marlowe@gmail.com>
To: Emiliano Leporati <emiliano.leporati@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: un-understood index performance behaviour
Date: Tue, 1 Jul 2008 08:17:17 -0600
Message-ID: <dcc563d10807010717v7e67dad1y56838f066efac6fb@mail.gmail.com> (raw)
In-Reply-To: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.com>
References: <c5f2bc170807010349o6d252e3cwcacb6e9c898235c8@mail.gmail.com>
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.
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: scott.marlowe@gmail.com, emiliano.leporati@gmail.com
Subject: Re: un-understood index performance behaviour
In-Reply-To: <dcc563d10807010717v7e67dad1y56838f066efac6fb@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