When I try to make query like this:
SELECT * FROM id.users ORDER BY login LIMIT 10,
in partitioned table, pgPool makes two queries:
SELECT columns FROM id.users
SELECT columns FROM id.users
Retrieves 20 000 000 records and after that sorts them and limits 10.
Query works near 20 minutes. As you see, it is not usable.
But you can make two qoueries:
SELECT columns FROM id.users ORDER BY login LIMIT 10; -- uses P-Key
for login field
SELECT columns FROM id.users ORDER BY login LIMIT 10; -- and orders
elements
And on the pgPool's storage you can make:
SELECT columns FROM merged_table ORDER BY login LIMIT 10;
So you will fetch only ten rows instead of 20 000 000.
If I will use OFFSET (which is stupid idea, conditions needed in this case):
SELECT * FROM id.users ORDER BY login LIMIT 10 OFFSET 10;
in partitioned tables pgPool will make:
SELECT columns FROM id.users ORDER BY login LIMIT 20;
SELECT columns FROM id.users ORDER BY login LIMIT 20;
and in merged table:
SELECT columns FROM id.users ORDER BY login LIMIT 10 OFFSET 10;
I can fix it, but I will take more time for it and will make a lot of
bugs, because I don't know your code. If you can, change behavior of
pgPool, or give me advices, how I can change it.
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
