I have a database with several tables with over 100 million rows. One of them has only two indexed attributes: id_document and id_set. Every document may belong to several sets. Then I need ids of all documents that belong to specified set. This result set may be very large, may contain more than 20 million rows and I need portions of 100 rows. So the query could be: select id_document from table limit x, 100. But when x is too high, the query is (of course) slow. I have also tried cursors in PostgreSQL, but it is still slow. Does anybody know, how to make it faster?

Depends how you want to use the info coming out of the database, and how often it changes. Can you execute the query once, then on your web server ( I'm assuming it's a web app ) write out an XML file with the results already broken into groups of 100?

I have also tried cursors in PostgreSQL, but it is still slow. Does anybody know, how to make it faster?

Not without seeing some of the queries.

Originally Posted by ForrestCroce

Depends how you want to use the info coming out of the database, and how often it changes. Can you execute the query once, then on your web server ( I'm assuming it's a web app ) write out an XML file with the results already broken into groups of 100?

Unfortunately, it won't solve my problem.

Originally Posted by chrishirst

Not without seeing some of the queries.

Ok, select id_document where id_set=something limit x, 100 is slow for high x.
Using cursor under PostgreSQL:
CREATE FUNCTION reffunc() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR select id_document where id_set=something;
RETURN ref;
END;
' LANGUAGE 'plpgsql';[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif]

BEGIN;
SELECT reffunc();
FETCH 100 IN "<unnamed cursor 1>";
COMMIT;

This is still slow.
[/font]

Don't be offended, but did you tried an analyze of your query to see if you could help it a bit by adding indexe(s) on some other fields ?

It may help the engine to limit the number of tuples to select.

Are you doing an ordering too ? This can be a pretty heavy speed penalization, as the engine have to order the datas before going you back the list of them.
If you do, you may have a speed improvement by doing the ordering in the front-end language, or on the result subset.


I haven't worked on pgsql for the last 6 years, so I don't know what changed in the mean time, but at this time it wasn't especially fast (when keeping the atomic transaction log), and I was using a mysql db to "cache" synthetic results of typical select members could run.

Too complex and specific to my previous situation maybe to explain in long upfront, but if you are interested, drop me a msg.

Does MySQL or PostgreSQL have anything like Oracle's rownum? Can you select everything into a temporary table and add an identity column? If you can use WHERE ID BETWEEN 100 AND 200, that might run a lot faster.