I'm having a tough time optimizing a read query from PostgreSQL 10. The query is as follows

SELECT mp.id_person, (
          WHEN mp.id_g_type = mc.id_g_type
               (and mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
         ) / CAST(COUNT(mp.id_g_type) AS decimal)) as probability
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type= mc.id_g_type
WHERE mp.id_person!= 28076 AND mc.id_person= 28076
GROUP BY mp.id_person
          WHEN mp.id_g_type = mc.id_g_type
               and (mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g2)
          THEN 1
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97

I've created an indices, one on the id_person column, the other one on id_g_type column. The significant_mg is not a table but a materialized view that contains columns id_person, id_g_type (both bigints) and columns g1 and g2 (both smallints).

The id 28076 in the where clause won't be fixed as I only use this id for testing purposes, it will be replaced by a variable when I'm done optimizing the select.

The result of explain analyze is as follows

"Finalize HashAggregate  (cost=140784.56..141226.84 rows=6552 width=40) (actual time=2869.754..2891.226 rows=14 loops=1)"
"  Group Key: mp.id_person"
"  Filter: (((count(CASE WHEN ((mp.id_g_type = mc.id_g_type) AND ((mc.g1 = mp.g1) OR (mc.g1 = mp.g2) OR (mc.g2 = mp.g1) OR (mc.g2 = mp.g1))) THEN 1 ELSE NULL::integer END))::numeric / (count(mp.id_g_type))::numeric) > 0.97)"
"  Rows Removed by Filter: 19818"
"  ->  Gather  (cost=136165.16..140293.13 rows=39314 width=24) (actual time=2796.706..2823.029 rows=59496 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial HashAggregate  (cost=135165.16..135361.73 rows=19657 width=24) (actual time=2744.110..2753.896 rows=19832 loops=3)"
"              Group Key: mp.id_person"
"              ->  Nested Loop  (cost=0.43..103303.58 rows=1593079 width=32) (actual time=11.259..1589.526 rows=1283988 loops=3)"
"                    ->  Parallel Seq Scan on significant_mg mc  (cost=0.00..44870.04 rows=82 width=12) (actual time=11.189..373.041 rows=66 loops=3)"
"                          Filter: (id_person = 28076)"
"                          Rows Removed by Filter: 1291777"
"                    ->  Index Scan using i_g_type on significant_mg mp  (cost=0.43..518.83 rows=19377 width=20) (actual time=0.041..11.846 rows=19454 loops=198)"
"                          Index Cond: (id_g_type = mc.id_g_type)"
"                          Filter: (id_person <> 28076)"
"                          Rows Removed by Filter: 1"
"Planning Time: 0.498 ms"
"Execution Time: 2893.309 ms"

Is there any way I could improve the query performance?

UPDATED (simplified) query:

SELECT mp.id_person
FROM significant_mg mp
INNER JOIN significant_mg mc on mp.id_g_type = mc.id_g_type
WHERE mp.id_person != 28076 AND mc.id_person = 28076
GROUP BY mp.id_person
          WHEN mc.g1 = mp.g1 or mc.g1 = mp.g2 or mc.g2 = mp.g1 or mc.g2 = mp.g1
          THEN 1
         ) / CAST(COUNT(mp.id_g_type) AS decimal) > 0.97 
  • +1 for a good first question - welcome to the forum! – Vérace Mar 17 '19 at 9:37

You are fundamentally asking it to do a lot of work, there is no way around that. You can perhaps shave some time off of this, but you won't get orders of magnitude improvement. If you explain some of the motivation behind this, perhaps there is a way to materialize the view in a different way which will lead to large improvements.

For just optimizing the query as given:

You could create an index on all four columns, leading with "id_g_type", so that the index scan can be turned into an index-only scan. You probably need to manually vacuum the materialized view to make this effective.

The reason it is doing a seq scan on "mc", rather than using the index on "id_person", is that the seq scan can make use of parallelism while the index scan can not. You can play around with "max_parallel_workers_per_gather" (or with alter table significant_mg set (parallel_workers = ...); to see how much you actually benefit from parallelism.

There is no point in including mp.id_g_type = mc.id_g_type in the WHENs, because the join condition will ensure all rows pass that criterion.

  • Thank you, for your response. I've updated the query in the original post. As for the reasoning behind the query. The table significant_mc contains details about a person's genes. Each gene [id_g_type] can consist of different alleles [g1 and g2 columns]. My goal is to write a query that for a person with provided ID finds possible parents based on the following equation: probability = (count of matching genes [the when clause]) / (count of all genes the child and parent have in common). In the end, I filter out results that have less than 97% probability of being a parent. – toucheqt Mar 17 '19 at 14:41
  • Is the combination (id_person, id_g_type) unique? Does every person have a full complement of id_g_type data, or can there be missing data (or a set of genes to genotype which evolves over time, so some people have data on different ones)? – jjanes Mar 17 '19 at 15:31
  • The tuple (id_person, id_g_type) is unique and yes, there can be some id_g_type data missing. Some person might have 150 records of id_g_types, others can have 200. – toucheqt Mar 17 '19 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.