Re: un-understood index performance behaviour

On Tue, Jul 1, 2008 at 4:49 AM, Emiliano Leporati
<emiliano(dot)leporati(at)gmail(dot)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.