#	exclusive	inclusive	rows x	rows	loops	node
1.	0.005	135.619	↑ 135.0	1	1	→ Unique (cost=4640.37..4641.72 rows=135 width=12) (actual time=135.614..135.619 rows=1 loops=1) Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder Buffers: shared hit=3320
2.	0.012	135.614	↑ 10.4	13	1	→ Sort (cost=4640.37..4640.71 rows=135 width=12) (actual time=135.613..135.614 rows=13 loops=1) Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder Sort Key: sectioncon1_.sco_lorder, contentind0_.cix_lcontentkey Sort Method: quicksort Memory: 25kB Buffers: shared hit=3320
3.	77.133	135.602	↑ 10.4	13	1	→ Nested Loop Semi Join (cost=919.37..4635.60 rows=135 width=12) (actual time=23.634..135.602 rows=13 loops=1) Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder Join Filter: (contentind0_.cix_lcontentkey = contentind2_.cix_lcontentkey) Buffers: shared hit=3320
4.	1.305	10.015	↓ 51.3	6922	1	→ Nested Loop (cost=785.93..1149.40 rows=135 width=20) (actual time=4.751..10.015 rows=6922 loops=1) Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_con_lkey, contentacc3_.coa_con_lkey Buffers: shared hit=1330
5.	0.019	4.758	↑ 1.0	8	1	→ Hash Join (cost=785.93..808.58 rows=8 width=16) (actual time=4.736..4.758 rows=8 loops=1) Output: sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_con_lkey, contentacc3_.coa_con_lkey Hash Cond: (sectioncon1_.sco_con_lkey = contentacc3_.coa_con_lkey) Buffers: shared hit=532
6.	0.019	0.019	↑ 1.0	8	1	→ Index Scan using idx_sco2_7 on public.tsectioncontent2 sectioncon1_ (cost=0.00..22.56 rows=8 width=12) (actual time=0.008..0.019 rows=8 loops=1) Output: sectioncon1_.sco_lkey, sectioncon1_.sco_con_lkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_bapproved, sectioncon1_.sco_dtetimestamp Index Cond: ((sectioncon1_.sco_bapproved = 1) AND (sectioncon1_.sco_mei_lkey = 303)) Buffers: shared hit=11
7.	0.767	4.720	↓ 6.0	4129	1	→ Hash (cost=777.37..777.37 rows=685 width=4) (actual time=4.720..4.720 rows=4129 loops=1) Output: contentacc3_.coa_con_lkey Buckets: 1024 Batches: 1 Memory Usage: 146kB Buffers: shared hit=521
8.	1.970	3.953	↓ 6.0	4129	1	→ HashAggregate (cost=770.52..777.37 rows=685 width=4) (actual time=3.310..3.953 rows=4129 loops=1) Output: contentacc3_.coa_con_lkey Buffers: shared hit=521
9.	1.424	1.983	↑ 1.0	4129	1	→ Bitmap Heap Scan on public.tconaccessright2 contentacc3_ (cost=172.26..760.20 rows=4129 width=4) (actual time=0.626..1.983 rows=4129 loops=1) Output: contentacc3_.coa_skey, contentacc3_.coa_con_lkey, contentacc3_.coa_grp_hkey, contentacc3_.coa_bread, contentacc3_.coa_bupdate, contentacc3_.coa_bdelete Recheck Cond: (contentacc3_.coa_grp_hkey = '1879233DDDE40197C2277F3177E29FE829291F7E'::bpchar) Filter: (contentacc3_.coa_bread = 1) Buffers: shared hit=521
10.	0.559	0.559	↑ 1.0	4129	1	→ Bitmap Index Scan on idx_coa_2 (cost=0.00..171.23 rows=4129 width=0) (actual time=0.559..0.559 rows=4129 loops=1) Index Cond: (contentacc3_.coa_grp_hkey = '1879233DDDE40197C2277F3177E29FE829291F7E'::bpchar) Buffers: shared hit=37
11.	3.952	3.952	↓ 78.6	865	8	→ Index Scan using idx_cix_2 on public.tcontentindex contentind0_ (cost=0.00..42.46 rows=11 width=4) (actual time=0.009..0.494 rows=865 loops=8) Output: contentind0_.cix_skey, contentind0_.cix_lcontentkey, contentind0_.cix_lcontentstatus, contentind0_.cix_dtepublishfrom, contentind0_.cix_dtepublishto, contentind0_.cix_lcontenttypekey, contentind0_.cix_lcategorykey, contentind0_.cix_spath, contentind0_.cix_svalue, contentind0_.cix_sordervalue, contentind0_.cix_fnumvalue Index Cond: (contentind0_.cix_lcontentkey = sectioncon1_.sco_con_lkey) Filter: ((contentind0_.cix_dtepublishfrom <= '2012-05-10 14:56:00'::timestamp without time zone) AND ((contentind0_.cix_dtepublishto IS NULL) OR (contentind0_.cix_dtepublishto > '2012-05-10 14:56:00'::timestamp without time zone)) AND (contentind0_.cix_lcontentstatus = 2)) Buffers: shared hit=798
12.	44.261	48.454	↓ 18.4	92	6922	→ Materialize (cost=133.44..3476.09 rows=5 width=4) (actual time=0.000..0.007 rows=92 loops=6922) Output: contentind2_.cix_lcontentkey Buffers: shared hit=1990
13.	3.422	4.193	↓ 18.4	92	1	→ Bitmap Heap Scan on public.tcontentindex contentind2_ (cost=133.44..3476.06 rows=5 width=4) (actual time=1.051..4.193 rows=92 loops=1) Output: contentind2_.cix_lcontentkey Filter: (((contentind2_.cix_spath)::text ~~ 'contenttype'::text) AND ((contentind2_.cix_svalue)::text = 'page-document'::text)) Buffers: shared hit=1990
14.	0.771	0.771	↑ 1.0	5489	1	→ Bitmap Index Scan on idx_cix_3 (cost=0.00..133.43 rows=5489 width=0) (actual time=0.771..0.771 rows=5489 loops=1) Index Cond: ((contentind2_.cix_spath)::text = 'contenttype'::text) Buffers: shared hit=24

"Unique  (cost=4640.37..4641.72 rows=135 width=12) (actual time=135.614..135.619 rows=1 loops=1)"
"  Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder"
"  Buffers: shared hit=3320"
"  ->  Sort  (cost=4640.37..4640.71 rows=135 width=12) (actual time=135.613..135.614 rows=13 loops=1)"
"        Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder"
"        Sort Key: sectioncon1_.sco_lorder, contentind0_.cix_lcontentkey"
"        Sort Method:  quicksort  Memory: 25kB"
"        Buffers: shared hit=3320"
"        ->  Nested Loop Semi Join  (cost=919.37..4635.60 rows=135 width=12) (actual time=23.634..135.602 rows=13 loops=1)"
"              Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder"
"              Join Filter: (contentind0_.cix_lcontentkey = contentind2_.cix_lcontentkey)"
"              Buffers: shared hit=3320"
"              ->  Nested Loop  (cost=785.93..1149.40 rows=135 width=20) (actual time=4.751..10.015 rows=6922 loops=1)"
"                    Output: contentind0_.cix_lcontentkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_con_lkey, contentacc3_.coa_con_lkey"
"                    Buffers: shared hit=1330"
"                    ->  Hash Join  (cost=785.93..808.58 rows=8 width=16) (actual time=4.736..4.758 rows=8 loops=1)"
"                          Output: sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_con_lkey, contentacc3_.coa_con_lkey"
"                          Hash Cond: (sectioncon1_.sco_con_lkey = contentacc3_.coa_con_lkey)"
"                          Buffers: shared hit=532"
"                          ->  Index Scan using idx_sco2_7 on public.tsectioncontent2 sectioncon1_  (cost=0.00..22.56 rows=8 width=12) (actual time=0.008..0.019 rows=8 loops=1)"
"                                Output: sectioncon1_.sco_lkey, sectioncon1_.sco_con_lkey, sectioncon1_.sco_mei_lkey, sectioncon1_.sco_lorder, sectioncon1_.sco_bapproved, sectioncon1_.sco_dtetimestamp"
"                                Index Cond: ((sectioncon1_.sco_bapproved = 1) AND (sectioncon1_.sco_mei_lkey = 303))"
"                                Buffers: shared hit=11"
"                          ->  Hash  (cost=777.37..777.37 rows=685 width=4) (actual time=4.720..4.720 rows=4129 loops=1)"
"                                Output: contentacc3_.coa_con_lkey"
"                                Buckets: 1024  Batches: 1  Memory Usage: 146kB"
"                                Buffers: shared hit=521"
"                                ->  HashAggregate  (cost=770.52..777.37 rows=685 width=4) (actual time=3.310..3.953 rows=4129 loops=1)"
"                                      Output: contentacc3_.coa_con_lkey"
"                                      Buffers: shared hit=521"
"                                      ->  Bitmap Heap Scan on public.tconaccessright2 contentacc3_  (cost=172.26..760.20 rows=4129 width=4) (actual time=0.626..1.983 rows=4129 loops=1)"
"                                            Output: contentacc3_.coa_skey, contentacc3_.coa_con_lkey, contentacc3_.coa_grp_hkey, contentacc3_.coa_bread, contentacc3_.coa_bupdate, contentacc3_.coa_bdelete"
"                                            Recheck Cond: (contentacc3_.coa_grp_hkey = '1879233DDDE40197C2277F3177E29FE829291F7E'::bpchar)"
"                                            Filter: (contentacc3_.coa_bread = 1)"
"                                            Buffers: shared hit=521"
"                                            ->  Bitmap Index Scan on idx_coa_2  (cost=0.00..171.23 rows=4129 width=0) (actual time=0.559..0.559 rows=4129 loops=1)"
"                                                  Index Cond: (contentacc3_.coa_grp_hkey = '1879233DDDE40197C2277F3177E29FE829291F7E'::bpchar)"
"                                                  Buffers: shared hit=37"
"                    ->  Index Scan using idx_cix_2 on public.tcontentindex contentind0_  (cost=0.00..42.46 rows=11 width=4) (actual time=0.009..0.494 rows=865 loops=8)"
"                          Output: contentind0_.cix_skey, contentind0_.cix_lcontentkey, contentind0_.cix_lcontentstatus, contentind0_.cix_dtepublishfrom, contentind0_.cix_dtepublishto, contentind0_.cix_lcontenttypekey, contentind0_.cix_lcategorykey, contentind0_.cix_spath, contentind0_.cix_svalue, contentind0_.cix_sordervalue, contentind0_.cix_fnumvalue"
"                          Index Cond: (contentind0_.cix_lcontentkey = sectioncon1_.sco_con_lkey)"
"                          Filter: ((contentind0_.cix_dtepublishfrom <= '2012-05-10 14:56:00'::timestamp without time zone) AND ((contentind0_.cix_dtepublishto IS NULL) OR (contentind0_.cix_dtepublishto > '2012-05-10 14:56:00'::timestamp without time zone)) AND (contentind0_.cix_lcontentstatus = 2))"
"                          Buffers: shared hit=798"
"              ->  Materialize  (cost=133.44..3476.09 rows=5 width=4) (actual time=0.000..0.007 rows=92 loops=6922)"
"                    Output: contentind2_.cix_lcontentkey"
"                    Buffers: shared hit=1990"
"                    ->  Bitmap Heap Scan on public.tcontentindex contentind2_  (cost=133.44..3476.06 rows=5 width=4) (actual time=1.051..4.193 rows=92 loops=1)"
"                          Output: contentind2_.cix_lcontentkey"
"                          Filter: (((contentind2_.cix_spath)::text ~~ 'contenttype'::text) AND ((contentind2_.cix_svalue)::text = 'page-document'::text))"
"                          Buffers: shared hit=1990"
"                          ->  Bitmap Index Scan on idx_cix_3  (cost=0.00..133.43 rows=5489 width=0) (actual time=0.771..0.771 rows=5489 loops=1)"
"                                Index Cond: ((contentind2_.cix_spath)::text = 'contenttype'::text)"
"                                Buffers: shared hit=24"
"Total runtime: 135.747 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	4.846 ms	3.6 %
Bitmap Index Scan	2	1.330 ms	1.0 %
Hash	1	0.767 ms	0.6 %
Hash Join	1	0.019 ms	0.0 %
HashAggregate	1	1.970 ms	1.5 %
Index Scan	2	3.971 ms	2.9 %
Materialize	1	44.261 ms	32.6 %
Nested Loop	1	1.305 ms	1.0 %
Nested Loop Semi Join	1	77.133 ms	56.9 %
Sort	1	0.012 ms	0.0 %
Unique	1	0.005 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
public.tconaccessright2	1	1.424 ms	1.1 %
Bitmap Heap Scan	1	1.424 ms	100.0 %
public.tcontentindex	2	7.374 ms	5.4 %
Bitmap Heap Scan	1	3.422 ms	46.4 %
Index Scan	1	3.952 ms	53.6 %
public.tsectioncontent2	1	0.019 ms	0.0 %
Index Scan	1	0.019 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 2EH

Colorize:

Columns visibility:

Per node type stats

Per table stats