From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | trouble converting several serial queries into a parallel query |
Date: | 2015-07-04 20:30:04 |
Message-ID: | FE3E351A-04EF-4777-B248-A29425D290AC@2xlp.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a very simple query that is giving me some issues due to the size of the database and the number of requests I make to it in order to compile the report I need:
A dumbed down version of the table and query:
CREATE TABLE a_to_b (
id_a INT NOT NULL REFERENCES table_a(id),
id_b INT NOT NULL REFERENCES table_b(id),
PRIMARY KEY (id_a, id_b)
);
SELECT id_a, id_b FROM a_2_b WHERE id_a = 1 LIMIT 5;
The problem is that the table has a few million records and I need to query it 30+ times in a row.
I'd like to improve this with a parallel search using `IN()`
SELECT id_a, id_b FROM a_2_b WHERE id_a = IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26.27,28,29,30);
That technique has generally fixed a lot of bottlenecks for us.
However I can't wrap my head around structuring it so that I can apply a limit based on the column -- so that I only get 5 records per id_a.
The table has columns that I would use for ordering in the future, but I'm fine with just getting random values right now .
Can anyone offer some suggestions? Thanks in advance.
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Mair | 2015-07-05 09:04:31 | Re: trouble converting several serial queries into a parallel query |
Previous Message | Joshua D. Drake | 2015-07-04 19:26:12 | Re: could not fork new process for connection: Resource temporarily unavailable |