Wednesday, January 30, 2013

Postgresql Query Parallelizing with dblink

0 comments
I have three tables with almost 10 millions of records in each. The tables are:
customer
account
tickets

"customer" table holds record for all kind of customers which are related to account or tickets.

We need to generate a report with of customer and its related accounts or tickets.
The basic sql is like:
select *
from
(
select c.custid,
case when a.accountid is not null then
      a.accountid
     when t.ticketid is not null then
      t.ticketid
end
as relatedid      
from customer as c
left join account as a  on c.custid = a.accountid and type ='Accounts'
left  join tickets as t on c.custid = t.ticketid and type ='HelpDesk'
where c.deleted = 0
) as q
where relatedid is not null

I have all the required indexes. But the query was taking too much time.
One of the bottleneck that I always feel with Postgresql is its lack of query parallelism technique. Good news is that, the great developers are working on it.
However, I have to improve the query performance at this moment. So I make a plan to divide the query in two parts and then execute each part asynchronously and then collect the result.

To achieve this, I make the function qry_parallel_fn. This function create two separate dblink connection conn1 and conn2 and execute two different queries in async mode.
There is a while loop which checks if both the connections have completed the task or not. If yes, then the function return results.

CREATE OR REPLACE FUNCTION qry_parallel_fn() RETURNS SETOF RECORD AS $$
DECLARE
   v_qry1 text;
   v_qry2 text;
   cur1 cursor is
   select *
   from dblink_get_result('conn1') as t1(custid int, relatedid int);
  
   cur2 cursor is
   select *
   from dblink_get_result('conn2') as t1(custid int, relatedid int);
  
   v_closed smallint;
  
BEGIN
   
     v_qry1 := 'select custid, accountid as relatedid from customer c inner join account a on c.custid = a.accountid where c.deleted = 0';
     RAISE NOTICE 'vqry1 = %' , v_qry1;
     v_qry2 := 'select custid, ticketid as relatedid from customer c inner join tickets as t on c.custid = t.ticketid where c.deleted = 0';
   PERFORM dblink_connect('conn1','dbname=rumman');
   PERFORM dblink_connect('conn2','dbname=rumman');
     PERFORM dblink_send_query('conn1',v_qry1);
     PERFORM dblink_send_query('conn2',v_qry2);
   
     v_closed := 0;
     WHILE v_closed <> 2 loop
       if check_conn_is_busy('conn1') = 0 then
          v_closed := v_closed + 1;
       end if;
       if check_conn_is_busy('conn2') = 0 then
          v_closed := v_closed + 1;
       end if;
     END LOOP;
   
     FOR rec IN cur1
     LOOP
       RETURN NEXT rec;
     END LOOP;
   
     FOR rec IN cur2
     LOOP
       RETURN NEXT rec;
     END LOOP;
   
     PERFORM dblink_disconnect('conn1');
     PERFORM dblink_disconnect('conn2');
   
     RETURN;
END;
$$
language 'plpgsql'

--Execute
--select * from test_fn() as t1(c int, d int);
-- select count(*) from test_fn() as t1(c int, d int);

CREATE OR REPLACE FUNCTION check_conn_is_busy(conn text) RETURNS INT AS $$
DECLARE
  v int;
BEGIN
   SELECT dblink_is_busy(conn) INTO v;
   RETURN v;
END;
$$
language 'plpgsql'

I was monitoring the server performance and found that it was using two cpu cores to get the result and improve the query timing a bit.

Leave a Reply

Labels