title: Can I split a query in multiple queries or create parallelism to speed a query?
body: <p>I have a table <code>avl_pool</code>, and I have a function to find on the map the link nearest to that <code>(x, y)</code> position.</p>
  
  <p>The performance of this select is very linear, the function require ~8 ms to execute. So calculate this select for 1000 rows require 8 seconds. Or as I show in this sample 20.000 rows require 162 seconds.</p>
  
  <pre><code>SELECT avl_id, x, y, azimuth, map.get_near_link(X, Y, AZIMUTH)
  FROM avl_db.avl_pool         
  WHERE avl_id between 1 AND 20000
  
  "Index Scan using avl_pool_pkey on avl_pool  (cost=0.43..11524.76 rows=19143 width=28) (actual time=8.793..162805.384 rows=20000 loops=1)"
  "  Index Cond: ((avl_id &gt;= 1) AND (avl_id &lt;= 20000))"
  "  Buffers: shared hit=19879838"
  "Planning time: 0.328 ms"
  "Execution time: 162812.113 ms"
  </code></pre>
  
  <p>Using pgAdmin I found out if execute half of the range on separated windows at the same time, the execution time is actually split in half. So looks like the server can handle multiple requests to that same table/function without problem.</p>
  
  <pre><code>-- windows 1
  SELECT avl_id, x, y, azimuth, map.get_near_link(X, Y, AZIMUTH)
  FROM avl_db.avl_pool         
  WHERE avl_id between 1 AND 10000 
  
  Total query runtime: 83792 ms.
  
  -- windows 2
  SELECT avl_id, x, y, azimuth, map.get_near_link(X, Y, AZIMUTH)
  FROM avl_db.avl_pool         
  WHERE avl_id between 10001 AND 20000
  
  Total query runtime: 84047 ms.
  </code></pre>
  
  <p>So how should I aproach this scenario to improve performance?. </p>
  
  <p>From the <code>C#</code> aproach I guess I can create multiple threads and each one send a portion of the range and then I join all the data in the client. So instead one query with 20k and 162 seconds, I could send 10 querys with 2000 rows and finish in ~16 seconds. Of course maybe there is an overhead cost in the join, but shouldn't be big compared with the 160 seconds.</p>
  
  <p>Or is there is a different aproach I should consider, even better if is a just sql solution?</p>
  
  <hr>
  
  <p>@PeterRing I dont think function code is relevant but anyway here is.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION map.get_near_link(
      x NUMERIC,
      y NUMERIC,
      azim NUMERIC)
    RETURNS map.get_near_link AS
  $BODY$
  DECLARE
      strPoint TEXT;
      sRow map.get_near_link;
    BEGIN
      strPoint = 'POINT('|| X || ' ' || Y || ')';
      RAISE DEBUG 'GetLink strPoint % -- Azim %', strPoint, Azim;
  
      WITH index_query AS (
          SELECT --Seg_ID,
                 Link_ID,
                 azimuth,
                 TRUNC(ST_Distance(ST_GeomFromText(strPoint,4326), geom  )*100000)::INTEGER AS distance,
                 sentido,
                 --ST_AsText(geom),
                 geom
          FROM map.vzla_seg S
          WHERE
              ABS(Azim - S.azimuth) &lt; 30 OR
              ABS(Azim - S.azimuth) &gt; 330
          ORDER BY
              geom &lt;-&gt; ST_GeomFromText(strPoint, 4326)
          LIMIT 101
      )
      SELECT i.Link_ID, i.Distance, i.Sentido, v.geom INTO sRow
      FROM
          index_query i INNER JOIN
          map.vzla_rto v ON i.link_id = v.link_id
      ORDER BY
          distance LIMIT 1;
  
      RAISE DEBUG 'GetLink distance % ', sRow.distance;
      IF sRow.distance &gt; 50 THEN
          sRow.link_id = -1;
      END IF;
  
      RETURN sRow;
    END;
  $BODY$
    LANGUAGE plpgsql IMMUTABLE
    COST 100;
  ALTER FUNCTION map.get_near_link(NUMERIC, NUMERIC, NUMERIC)
    OWNER TO postgres;
  </code></pre>