#	exclusive	inclusive	rows x	rows	loops	node
1.	109.548	521.722	↑ 1.0	64084	1	→ Sort (cost=33789.64..33949.85 rows=64084 width=66) (actual time=498.826..521.722 rows=64084 loops=1) Sort Key: cell_line_mirna_qty.quantity Sort Method: external merge Disk: 5040kB
2.	45.644	412.174	↑ 1.0	64084	1	→ Hash Join (cost=7969.14..26044.77 rows=64084 width=66) (actual time=98.261..412.174 rows=64084 loops=1) Hash Cond: (cell_line_mirna_qty.id_mirna = mirna.id)
3.	54.206	357.497	↑ 1.0	64084	1	→ Hash Join (cost=7130.17..22499.23 rows=64084 width=61) (actual time=89.215..357.497 rows=64084 loops=1) Hash Cond: ((cell_line_gene_qty.id_transcript)::text = (gene.accession)::text)
4.	24.656	288.676	↑ 1.0	64084	1	→ Hash Join (cost=5909.27..18271.44 rows=64084 width=64) (actual time=74.588..288.676 rows=64084 loops=1) Hash Cond: (base_targeting_result.id_mirna = cell_line_mirna_qty.id_mirna)
5.	32.704	263.798	↑ 1.0	64084	1	→ Hash Join (cost=5891.12..17372.13 rows=64084 width=52) (actual time=74.357..263.798 rows=64084 loops=1) Hash Cond: ((base_targeting_result.id_transcript)::text = (cell_line_gene_qty.id_transcript)::text)
6.	53.220	226.307	↑ 1.0	64084	1	→ Hash Join (cost=5568.29..16168.15 rows=64084 width=38) (actual time=69.561..226.307 rows=64084 loops=1) Hash Cond: (result_base_mctarget_items.parent_id = base_targeting_result.id)
7.	30.672	103.934	↑ 1.0	64084	1	→ Merge Join (cost=0.00..8105.07 rows=64084 width=24) (actual time=0.062..103.934 rows=64084 loops=1) Merge Cond: (result_base_mctarget_items.id = result_mctarget_items.id)
8.	31.790	58.760	↑ 1.0	64084	1	→ Merge Join (cost=0.00..5062.29 rows=64084 width=16) (actual time=0.042..58.760 rows=64084 loops=1) Merge Cond: (result_base_mctarget_items.id = result_mctarget_stable_items.id)
9.	14.121	14.121	↑ 1.0	64084	1	→ Index Scan using result_base_mctarget_items_pkey on result_base_mctarget_items (cost=0.00..2144.52 rows=64084 width=8) (actual time=0.019..14.121 rows=64084 loops=1)
10.	12.849	12.849	↑ 1.0	64084	1	→ Index Scan using result_mctarget_stable_items_pkey on result_mctarget_stable_items (cost=0.00..1956.52 rows=64084 width=8) (actual time=0.019..12.849 rows=64084 loops=1)
11.	14.502	14.502	↑ 1.0	64084	1	→ Index Scan using result_mctarget_items_pkey on result_mctarget_items (cost=0.00..2081.52 rows=64084 width=20) (actual time=0.018..14.502 rows=64084 loops=1)
12.	17.205	69.153	↑ 1.0	55663	1	→ Hash (cost=4491.50..4491.50 rows=55663 width=26) (actual time=69.153..69.153 rows=55663 loops=1)
13.	26.063	51.948	↑ 1.0	55663	1	→ Merge Join (cost=0.00..4491.50 rows=55663 width=26) (actual time=0.041..51.948 rows=55663 loops=1) Merge Cond: (base_targeting_result.id = result_mctarget.id)
14.	10.042	10.042	↑ 1.0	55663	1	→ Index Scan using base_targeting_result_pkey on base_targeting_result (cost=0.00..1816.20 rows=55663 width=18) (actual time=0.019..10.042 rows=55663 loops=1)
15.	15.843	15.843	↑ 1.0	55663	1	→ Index Scan using result_mctarget_pkey on result_mctarget (cost=0.00..1840.36 rows=55663 width=8) (actual time=0.020..15.843 rows=55663 loops=1) Filter: ((result_mctarget.overexpressed_mirna IS NULL) AND (result_mctarget.id_cell_line = 1))
16.	1.787	4.787	↑ 1.0	7416	1	→ Hash (cost=230.13..230.13 rows=7416 width=22) (actual time=4.787..4.787 rows=7416 loops=1)
17.	1.560	3.000	↑ 1.0	7416	1	→ Nested Loop (cost=0.00..230.13 rows=7416 width=22) (actual time=0.024..3.000 rows=7416 loops=1)
18.	0.014	0.014	↑ 1.0	1	1	→ Index Scan using cell_line_pkey on cell_line (cost=0.00..8.27 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: (id = 1)
19.	1.426	1.426	↑ 1.0	7416	1	→ Seq Scan on cell_line_gene_qty (cost=0.00..147.70 rows=7416 width=18) (actual time=0.008..1.426 rows=7416 loops=1) Filter: (cell_line_gene_qty.id_cell_line = 1)
20.	0.098	0.222	↑ 1.0	566	1	→ Hash (cost=11.07..11.07 rows=566 width=12) (actual time=0.222..0.222 rows=566 loops=1)
21.	0.124	0.124	↑ 1.0	566	1	→ Seq Scan on cell_line_mirna_qty (cost=0.00..11.07 rows=566 width=12) (actual time=0.011..0.124 rows=566 loops=1) Filter: (id_cell_line = 1)
22.	8.711	14.615	↑ 1.0	31818	1	→ Hash (cost=636.18..636.18 rows=31818 width=17) (actual time=14.615..14.615 rows=31818 loops=1)
23.	5.904	5.904	↑ 1.0	31818	1	→ Seq Scan on gene (cost=0.00..636.18 rows=31818 width=17) (actual time=0.007..5.904 rows=31818 loops=1)
24.	4.838	9.033	↑ 1.0	21643	1	→ Hash (cost=441.43..441.43 rows=21643 width=17) (actual time=9.033..9.033 rows=21643 loops=1)
25.	4.195	4.195	↑ 1.0	21643	1	→ Seq Scan on mirna (cost=0.00..441.43 rows=21643 width=17) (actual time=0.012..4.195 rows=21643 loops=1)

Sort  (cost=33789.64..33949.85 rows=64084 width=66) (actual time=498.826..521.722 rows=64084 loops=1)
  Sort Key: cell_line_mirna_qty.quantity
  Sort Method:  external merge  Disk: 5040kB
  ->  Hash Join  (cost=7969.14..26044.77 rows=64084 width=66) (actual time=98.261..412.174 rows=64084 loops=1)
        Hash Cond: (cell_line_mirna_qty.id_mirna = mirna.id)
        ->  Hash Join  (cost=7130.17..22499.23 rows=64084 width=61) (actual time=89.215..357.497 rows=64084 loops=1)
              Hash Cond: ((cell_line_gene_qty.id_transcript)::text = (gene.accession)::text)
              ->  Hash Join  (cost=5909.27..18271.44 rows=64084 width=64) (actual time=74.588..288.676 rows=64084 loops=1)
                    Hash Cond: (base_targeting_result.id_mirna = cell_line_mirna_qty.id_mirna)
                    ->  Hash Join  (cost=5891.12..17372.13 rows=64084 width=52) (actual time=74.357..263.798 rows=64084 loops=1)
                          Hash Cond: ((base_targeting_result.id_transcript)::text = (cell_line_gene_qty.id_transcript)::text)
                          ->  Hash Join  (cost=5568.29..16168.15 rows=64084 width=38) (actual time=69.561..226.307 rows=64084 loops=1)
                                Hash Cond: (result_base_mctarget_items.parent_id = base_targeting_result.id)
                                ->  Merge Join  (cost=0.00..8105.07 rows=64084 width=24) (actual time=0.062..103.934 rows=64084 loops=1)
                                      Merge Cond: (result_base_mctarget_items.id = result_mctarget_items.id)
                                      ->  Merge Join  (cost=0.00..5062.29 rows=64084 width=16) (actual time=0.042..58.760 rows=64084 loops=1)
                                            Merge Cond: (result_base_mctarget_items.id = result_mctarget_stable_items.id)
                                            ->  Index Scan using result_base_mctarget_items_pkey on result_base_mctarget_items  (cost=0.00..2144.52 rows=64084 width=8) (actual time=0.019..14.121 rows=64084 loops=1)
                                            ->  Index Scan using result_mctarget_stable_items_pkey on result_mctarget_stable_items  (cost=0.00..1956.52 rows=64084 width=8) (actual time=0.019..12.849 rows=64084 loops=1)
                                      ->  Index Scan using result_mctarget_items_pkey on result_mctarget_items  (cost=0.00..2081.52 rows=64084 width=20) (actual time=0.018..14.502 rows=64084 loops=1)
                                ->  Hash  (cost=4491.50..4491.50 rows=55663 width=26) (actual time=69.153..69.153 rows=55663 loops=1)
                                      ->  Merge Join  (cost=0.00..4491.50 rows=55663 width=26) (actual time=0.041..51.948 rows=55663 loops=1)
                                            Merge Cond: (base_targeting_result.id = result_mctarget.id)
                                            ->  Index Scan using base_targeting_result_pkey on base_targeting_result  (cost=0.00..1816.20 rows=55663 width=18) (actual time=0.019..10.042 rows=55663 loops=1)
                                            ->  Index Scan using result_mctarget_pkey on result_mctarget  (cost=0.00..1840.36 rows=55663 width=8) (actual time=0.020..15.843 rows=55663 loops=1)
                                                  Filter: ((result_mctarget.overexpressed_mirna IS NULL) AND (result_mctarget.id_cell_line = 1))
                          ->  Hash  (cost=230.13..230.13 rows=7416 width=22) (actual time=4.787..4.787 rows=7416 loops=1)
                                ->  Nested Loop  (cost=0.00..230.13 rows=7416 width=22) (actual time=0.024..3.000 rows=7416 loops=1)
                                      ->  Index Scan using cell_line_pkey on cell_line  (cost=0.00..8.27 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)
                                            Index Cond: (id = 1)
                                      ->  Seq Scan on cell_line_gene_qty  (cost=0.00..147.70 rows=7416 width=18) (actual time=0.008..1.426 rows=7416 loops=1)
                                            Filter: (cell_line_gene_qty.id_cell_line = 1)
                    ->  Hash  (cost=11.07..11.07 rows=566 width=12) (actual time=0.222..0.222 rows=566 loops=1)
                          ->  Seq Scan on cell_line_mirna_qty  (cost=0.00..11.07 rows=566 width=12) (actual time=0.011..0.124 rows=566 loops=1)
                                Filter: (id_cell_line = 1)
              ->  Hash  (cost=636.18..636.18 rows=31818 width=17) (actual time=14.615..14.615 rows=31818 loops=1)
                    ->  Seq Scan on gene  (cost=0.00..636.18 rows=31818 width=17) (actual time=0.007..5.904 rows=31818 loops=1)
        ->  Hash  (cost=441.43..441.43 rows=21643 width=17) (actual time=9.033..9.033 rows=21643 loops=1)
              ->  Seq Scan on mirna  (cost=0.00..441.43 rows=21643 width=17) (actual time=0.012..4.195 rows=21643 loops=1)
Total runtime: 525.786 ms

Per node type stats

node type	count	sum of times	% of query
Hash	5	32.639 ms	6.3 %
Hash Join	5	210.430 ms	40.3 %
Index Scan	6	67.371 ms	12.9 %
Merge Join	3	88.525 ms	17.0 %
Nested Loop	1	1.560 ms	0.3 %
Seq Scan	4	11.649 ms	2.2 %
Sort	1	109.548 ms	21.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
base_targeting_result	1	10.042 ms	1.9 %
Index Scan	1	10.042 ms	100.0 %
cell_line	1	0.014 ms	0.0 %
Index Scan	1	0.014 ms	100.0 %
cell_line_gene_qty	1	1.426 ms	0.3 %
Seq Scan	1	1.426 ms	100.0 %
cell_line_mirna_qty	1	0.124 ms	0.0 %
Seq Scan	1	0.124 ms	100.0 %
gene	1	5.904 ms	1.1 %
Seq Scan	1	5.904 ms	100.0 %
mirna	1	4.195 ms	0.8 %
Seq Scan	1	4.195 ms	100.0 %
result_base_mctarget_items	1	14.121 ms	2.7 %
Index Scan	1	14.121 ms	100.0 %
result_mctarget	1	15.843 ms	3.0 %
Index Scan	1	15.843 ms	100.0 %
result_mctarget_items	1	14.502 ms	2.8 %
Index Scan	1	14.502 ms	100.0 %
result_mctarget_stable_items	1	12.849 ms	2.5 %
Index Scan	1	12.849 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: urU

Colorize:

Columns visibility:

Per node type stats

Per table stats