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.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Let me know your thoughts on it.
Thanks.
