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