Search Postgresql Archives
Re: problem with query
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
- Subject: Re: problem with query
- From: Sašo Gantar <sasog23@xxxxxxxxx>
- Date: Mon, 20 May 2024 13:08:43 +0200
- Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
- In-reply-to: <CAApHDvq=sj+vVQ6HKOdNLeWbQnHErg5QPSz26s0a90PtY1NLdw@mail.gmail.com>
- References: <CAGB0_6600w5C=hvhgfMWCqO9BCwCg+3s0PxXuoQv48NLqTp6dA@mail.gmail.com> <CAApHDvq=sj+vVQ6HKOdNLeWbQnHErg5QPSz26s0a90PtY1NLdw@mail.gmail.com>
Aggregate (cost=512.53..512.54 rows=1 width=32) (actual time=8430.692..8430.724 rows=1 loops=1)
Buffers: shared hit=2031540, temp read=954 written=956
-> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152) (actual time=8257.310..8430.532 rows=57 loops=1)
Buffers: shared hit=2031540, temp read=954 written=956
-> GroupAggregate (cost=510.85..512.50 rows=2 width=324) (actual time=8257.304..8430.427 rows=57 loops=1)
Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
Buffers: shared hit=2031540, temp read=954 written=956
-> Sort (cost=510.85..510.85 rows=2 width=582) (actual time=8253.824..8258.370 rows=12994 loops=1)
Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
Sort Method: external merge Disk: 7632kB
Buffers: shared hit=2004085, temp read=954 written=956
-> Nested Loop Left Join (cost=353.35..510.84 rows=2 width=582) (actual time=25.558..8232.211 rows=12994 loops=1)
Join Filter: (nc_1.nspname = pgn.nspname)
Buffers: shared hit=2004074
-> Nested Loop Left Join (cost=352.79..455.74 rows=2 width=519) (actual time=25.548..8207.051 rows=12994 loops=1)
Join Filter: (nc.nspname = pgn.nspname)
Buffers: shared hit=1963631
-> Nested Loop Left Join (cost=350.04..447.39 rows=2 width=487) (actual time=25.527..8049.285 rows=12994 loops=1)
Buffers: shared hit=1719586
-> Nested Loop Left Join (cost=349.76..446.26 rows=1 width=417) (actual time=25.509..8039.922 rows=429 loops=1)
Join Filter: (((ist.event_object_schema)::name = pgn.nspname) AND ((ist.event_object_table)::name = pgc.relname) AND ((ist.trigger_name)::name = pgt.tgname))
Rows Removed by Join Filter: 43600
Buffers: shared hit=1711154
-> Nested Loop Left Join (cost=39.75..81.15 rows=1 width=201) (actual time=0.209..1.186 rows=401 loops=1)
Buffers: shared hit=329
-> Nested Loop (cost=39.47..80.56 rows=1 width=133) (actual time=0.179..0.475 rows=57 loops=1)
Buffers: shared hit=65
-> Index Scan using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49 rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=1)
Index Cond: (nspname = 'servicedesk'::name)
Buffers: shared hit=3
-> Bitmap Heap Scan on pg_class pgc (cost=39.19..77.93 rows=14 width=73) (actual time=0.171..0.432 rows=57 loops=1)
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
Rows Removed by Filter: 163
Heap Blocks: exact=44
Buffers: shared hit=62
-> Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..39.19 rows=53 width=0) (actual time=0.158..0.158 rows=220 loops=1)
Index Cond: (relnamespace = pgn.oid)
Buffers: shared hit=18
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7 width=72) (actual time=0.005..0.009 rows=7 loops=57)
Index Cond: (tgrelid = pgc.oid)
Buffers: shared hit=264
-> Subquery Scan on ist (cost=310.01..365.05 rows=3 width=408) (actual time=11.344..20.034 rows=109 loops=401)
Filter: ((ist.event_object_schema)::name = 'servicedesk'::name)
Rows Removed by Filter: 364
Buffers: shared hit=1710825
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
-> Sort (cost=310.01..311.35 rows=537 width=438) (actual time=0.008..0.042 rows=473 loops=401)
Sort Key: ((n.nspname)::information_schema.sql_identifier), ((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1, (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
Sort Method: quicksort Memory: 268kB
Buffers: shared hit=2056
-> Nested Loop (cost=195.66..285.66 rows=537 width=438) (actual time=2.143..2.752 rows=473 loops=1)
Join Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
Rows Removed by Join Filter: 679
Buffers: shared hit=2053
-> Hash Join (cost=195.66..269.45 rows=180 width=330) (actual time=2.136..2.532 rows=384 loops=1)
Hash Cond: (t_1.tgrelid = c_2.oid)
Buffers: shared hit=2053
-> Seq Scan on pg_trigger t_1 (cost=0.00..70.55 rows=383 width=202) (actual time=0.005..0.313 rows=384 loops=1)
Filter: (NOT tgisinternal)
Rows Removed by Filter: 1872
Buffers: shared hit=48
-> Hash (cost=181.57..181.57 rows=1127 width=136) (actual time=2.120..2.123 rows=2401 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 426kB
Buffers: shared hit=2005
-> Hash Join (cost=23.06..181.57 rows=1127 width=136) (actual time=0.892..1.695 rows=2401 loops=1)
Hash Cond: (c_2.relnamespace = n.oid)
Buffers: shared hit=2005
-> Seq Scan on pg_class c_2 (cost=0.00..154.04 rows=1690 width=76) (actual time=0.003..0.449 rows=2401 loops=1)
Filter: (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE, REFERENCES'::text))
Buffers: shared hit=58
-> Hash (cost=17.44..17.44 rows=450 width=68) (actual time=0.868..0.869 rows=47 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=1947
-> Seq Scan on pg_namespace n (cost=0.00..17.44 rows=450 width=68) (actual time=0.003..0.859 rows=47 loops=1)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 642
Buffers: shared hit=1947
-> Materialize (cost=0.00..0.05 rows=3 width=36) (actual time=0.000..0.000 rows=3 loops=384)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual time=0.003..0.004 rows=3 loops=1)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.28..1.06 rows=7 width=70) (actual time=0.005..0.016 rows=30 loops=429)
Index Cond: (attrelid = pgc.oid)
Buffers: shared hit=8432
-> Nested Loop Left Join (cost=2.76..4.16 rows=1 width=224) (actual time=0.011..0.012 rows=1 loops=12994)
Buffers: shared hit=244045
-> Nested Loop (cost=2.21..3.52 rows=1 width=228) (actual time=0.010..0.011 rows=1 loops=12994)
Buffers: shared hit=219133
-> Nested Loop (cost=1.93..3.17 rows=1 width=627) (actual time=0.006..0.007 rows=1 loops=12994)
Buffers: shared hit=187599
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76) (actual time=0.001..0.001 rows=1 loops=12994)
Index Cond: (relname = pgc.relname)
Filter: (relkind = ANY ('{r,v,m,f,p}'::"char"[]))
Buffers: shared hit=39712
-> Nested Loop (cost=1.65..2.72 rows=1 width=571) (actual time=0.005..0.005 rows=1 loops=13724)
Buffers: shared hit=147887
-> Nested Loop Left Join (cost=1.38..2.39 rows=1 width=503) (actual time=0.004..0.004 rows=1 loops=13724)
Buffers: shared hit=116864
-> Nested Loop Left Join (cost=1.10..2.07 rows=1 width=294) (actual time=0.003..0.003 rows=1 loops=13724)
Join Filter: (t.typtype = 'd'::"char")
Buffers: shared hit=92576
-> Nested Loop (cost=0.55..1.32 rows=1 width=160) (actual time=0.002..0.002 rows=1 loops=13724)
Buffers: shared hit=71866
-> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a (cost=0.28..1.00 rows=1 width=80) (actual time=0.001..0.001 rows=1 loops=13724)
Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 0
Buffers: shared hit=40843
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.27..0.32 rows=1 width=84) (actual time=0.001..0.001 rows=1 loops=10341)
Index Cond: (oid = a.atttypid)
Buffers: shared hit=31023
-> Nested Loop (cost=0.55..0.74 rows=1 width=138) (actual time=0.001..0.001 rows=0 loops=10341)
Buffers: shared hit=20710
-> Index Scan using pg_type_oid_index on pg_type bt (cost=0.27..0.42 rows=1 width=78) (actual time=0.001..0.001 rows=0 loops=10341)
Index Cond: (oid = t.typbasetype)
Buffers: shared hit=20689
-> Index Scan using pg_namespace_oid_index on pg_namespace nbt (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=7)
Index Cond: (oid = bt.typnamespace)
Buffers: shared hit=21
-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.28..0.32 rows=1 width=211) (actual time=0.001..0.001 rows=0 loops=10341)
Index Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
Buffers: shared hit=24288
-> Index Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=10341)
Index Cond: (oid = c.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
Rows Removed by Filter: 0
Buffers: shared hit=31023
-> Index Scan using pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=10150)
Index Cond: (oid = t.typnamespace)
Buffers: shared hit=30450
-> Nested Loop (cost=0.55..0.63 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10150)
Buffers: shared hit=24912
-> Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.31 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=10150)
Index Cond: (oid = a.attcollation)
Buffers: shared hit=21453
-> Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1153)
Index Cond: (oid = co.collnamespace)
Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
Rows Removed by Filter: 1
Buffers: shared hit=3459
-> Nested Loop (cost=0.56..27.52 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=12994)
Buffers: shared hit=40443
-> Index Scan using pg_class_relname_nsp_index on pg_class c_1 (cost=0.28..0.46 rows=1 width=76) (actual time=0.001..0.001 rows=0 loops=12994)
Index Cond: (relname = pgc.relname)
Filter: ((relkind = ANY ('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 1
Buffers: shared hit=39712
-> Index Scan using pg_namespace_oid_index on pg_namespace nc_1 (cost=0.28..0.43 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=9)
Index Cond: (oid = c_1.relnamespace)
Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
Buffers: shared hit=27
SubPlan 1
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 81) = 81)
Buffers: shared hit=18
SubPlan 2
-> Seq Scan on pg_trigger pg_trigger_1 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 81) = 81)
SubPlan 3
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2 (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 73) = 73)
Buffers: shared hit=18
SubPlan 4
-> Seq Scan on pg_trigger pg_trigger_3 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 73) = 73)
SubPlan 5
-> Index Scan using pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4 (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
Index Cond: (tgrelid = c_1.oid)
Filter: (((tgtype)::integer & 69) = 69)
Buffers: shared hit=18
SubPlan 6
-> Seq Scan on pg_trigger pg_trigger_5 (cost=0.00..87.46 rows=11 width=4) (never executed)
Filter: (((tgtype)::integer & 69) = 69)
Planning:
Buffers: shared hit=498
Planning Time: 7.419 ms
Execution Time: 8432.371 ms
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]
[Postgresql Jobs]
[Postgresql Admin]
[Postgresql Performance]
[Linux Clusters]
[PHP Home]
[PHP on Windows]
[Kernel Newbies]
[PHP Classes]
[PHP Databases]
[Postgresql & PHP]
[Yosemite]
