[GENERAL] Query which "never" terminates

Поиск

Список

Период

Сортировка

От	Viktor Fougstedt
Тема	[GENERAL] Query which "never" terminates
Дата	27 апреля 2017 г. 19:09:42
Msg-id	C8A06397-263A-45C3-B858-4EBB302246BC@chalmers.se обсуждение исходный текст
Ответы	Re: [GENERAL] Query which "never" terminates (pinker <pinker@onet.eu>)
Список	pgsql-general

Дерево обсуждения

Dear sirs.

I have an issue which I am at a loss to solve. Because of the behaviour I am not certain that this is a “slow query” problem, so I send my question here.

My question regards a query using deep subqueries. EXPLAIN gives a maximum cost of about 2.000 (details below), but the query runs "forever" (at least 3 hours). It takes 100% CPU on the machine while running. It is expected to produce 0 rows. I have auto-vacuum enabled, but have also manually VACUUM:ed and ANALYZE:d the database.

I am using "PostgreSQL 9.4.9 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit)".

The query has worked as expected before, but stopped working. See below for query, EXPLAIN-output, definition of all tables, the table's sizes, and a gdb backtrace of the "hung" process. I apologize for the SQL, which is horribly ugly. It is in part automatically generated, and therefore looks like ****. I have tried to format it in an as-readable-form as possible.

I have manually executed the innermost subquery ("SELECT n.account, ..."), and it produces 1 row in negligible time.The next levels out ("SELECT s3.nid, ...", "SELECT s4.nid, ..." and "SELECT i.assignment, ...") all produce 0 rows and run fine.

I have run "CREATE TABLE tmptbl AS SELECT i.assignment, ...", running the topmost subquery and all its subqueries. They produce 0 rows in negligible time. If I replace the subquery with a select from that temporary table, "SELECT s6.nid AS orgunit, i.tags_point AS tags_point FROM tmptbl, ...", that query runs fine, producing 0 rows in notime.

It is only when I use the complete query that it hangs - the individual parts all run.

A "select * from pg_stat_activity where waiting='t'” while the query is running produces 0 rows.

I also think that locking problems are unlikely, since the query works if I split it in two and use a table to store
intermediate output (which is 0 rows, BTW). Unless an inner query can hang on a lock held by an outer one.

The EXPLAIN (since it does not terminate I cannot run EXPLAIN ANALYZE): https://explain.depesz.com/s/Ex2z

See below for all the ugly details.

Thank you in advance, for all and any help.

/Viktor

SELECT s6.nid AS orgunit, i.tags_point AS tags_point
FROM (SELECT i.assignment AS assignment, i.tags_point AS tags_point
FROM (SELECT s4.nid AS assignment, i.tags_point AS tags_point
FROM (SELECT s3.nid AS person, i.tags_point AS tags_point
FROM (SELECT n.account AS account, tp.point AS tags_point
FROM "pdb4_ad"."account_name" n,
UNNEST(ARRAY[0]) AS injected_points,
tags_point tp
WHERE injected_points = tp.point
AND int8range(n._r_from, n._r_to) @> tp.rev
AND (n._value = '__bootstrap__')
AND tsrange(n._start, n._stop) @> tp.time
) i,
tags_point AS tp,
"pdb4_ad"."account" AS s1,
"pdb4_ad"."identifies" AS s2,
"pdb4_person"."person" AS s3
WHERE i.tags_point = tp.point
AND i.account = s1.nid
AND s1.nid = s2."account"
AND s2."person" = s3.nid
AND INT8RANGE(s1._r_from, s1._r_to) @> tp.rev
AND TSRANGE(s1._start, s1._stop) @> tp.time
AND INT8RANGE(s2._r_from, s2._r_to) @> tp.rev
AND TSRANGE(s2._start, s2._stop) @> tp.time
AND INT8RANGE(s3._r_from, s3._r_to) @> tp.rev
AND TSRANGE(s3._start, s3._stop) @> tp.time
) i,
tags_point AS tp,
"pdb4_person"."person" AS s1,
"pdb4_org"."assignment" AS s2,
"pdb4_org"."assignment_active" AS s3,
"pdb4_org"."assignment" AS s4
WHERE i.tags_point = tp.point
AND i.person = s1.nid
AND s1.nid = s2."person"
AND s2.nid = s3."assignment"
AND s3."assignment" = s4.nid
AND INT8RANGE(s1._r_from, s1._r_to) @> tp.rev
AND TSRANGE(s1._start, s1._stop) @> tp.time
AND INT8RANGE(s2._r_from, s2._r_to) @> tp.rev
AND TSRANGE(s2._start, s2._stop) @> tp.time
AND INT8RANGE(s3._r_from, s3._r_to) @> tp.rev
AND TSRANGE(s3._start, s3._stop) @> tp.time
AND INT8RANGE(s4._r_from, s4._r_to) @> tp.rev
AND TSRANGE(s4._start, s4._stop) @> tp.time
) i,
tags_point AS tp,
"pdb4_org"."assignment" AS s1,
"pdb4_org"."role" AS s2,
"pdb4_org"."role_name" AS s3
WHERE i.tags_point = tp.point
AND i.assignment = s1.nid
AND s1."role" = s2.nid
AND s2."name" = s3.nid
AND INT8RANGE(s1._r_from, s1._r_to) @> tp.rev
AND TSRANGE(s1._start, s1._stop) @> tp.time
AND INT8RANGE(s2._r_from, s2._r_to) @> tp.rev
AND TSRANGE(s2._start, s2._stop) @> tp.time
AND INT8RANGE(s3._r_from, s3._r_to) @> tp.rev
AND TSRANGE(s3._start, s3._stop) @> tp.time
AND s3._value = 'pdb-reggare'
) i,
tags_point AS tp,
"pdb4_org"."assignment" AS s1,
"pdb4_org"."orgunit" AS s2,
"pdb4_org"."orgunit_path" AS s3,
"pdb4_org"."orgunit_path_validity" AS s4,
"pdb4_org"."orgunit_path" AS s5,
"pdb4_org"."orgunit" AS s6
WHERE i.tags_point = tp.point
AND i.assignment = s1.nid
AND s1."orgunit" = s2.nid
AND s2.nid = s3."end"
AND s3.nid = s4."path"
AND s4."path" = s5.nid
AND s5."start" = s6.nid
AND INT8RANGE(s1._r_from, s1._r_to) @> tp.rev
AND TSRANGE(s1._start, s1._stop) @> tp.time
AND INT8RANGE(s2._r_from, s2._r_to) @> tp.rev
AND TSRANGE(s2._start, s2._stop) @> tp.time
AND INT8RANGE(s3._r_from, s3._r_to) @> tp.rev
AND TSRANGE(s3._start, s3._stop) @> tp.time
AND INT8RANGE(s4._r_from, s4._r_to) @> tp.rev
AND TSRANGE(s4._start, s4._stop) @> tp.time
AND INT8RANGE(s5._r_from, s5._r_to) @> tp.rev
AND TSRANGE(s5._start, s5._stop) @> tp.time
AND INT8RANGE(s6._r_from, s6._r_to) @> tp.rev
AND TSRANGE(s6._start, s6._stop) @> tp.time

A backtrace of the postgres process executing the query:

#0 0x000000010739fdb7 in ExecQual ()
#1 0x00000001073a0c9f in ExecScan ()
#2 0x000000010739a5b6 in ExecProcNode ()
#3 0x00000001073af88b in ExecNestLoop ()
#4 0x000000010739a645 in ExecProcNode ()
#5 0x00000001073af88b in ExecNestLoop ()
#6 0x000000010739a645 in ExecProcNode ()
#7 0x00000001073af88b in ExecNestLoop ()
#8 0x000000010739a645 in ExecProcNode ()
#9 0x00000001073af88b in ExecNestLoop ()
#10 0x000000010739a645 in ExecProcNode ()
#11 0x00000001073af88b in ExecNestLoop ()
#12 0x000000010739a645 in ExecProcNode ()
#13 0x00000001073af88b in ExecNestLoop ()
#14 0x000000010739a645 in ExecProcNode ()
#15 0x00000001073af88b in ExecNestLoop ()
#16 0x000000010739a645 in ExecProcNode ()
#17 0x00000001073af88b in ExecNestLoop ()
#18 0x000000010739a645 in ExecProcNode ()
#19 0x00000001073af88b in ExecNestLoop ()
#20 0x000000010739a645 in ExecProcNode ()
#21 0x00000001073af88b in ExecNestLoop ()
#22 0x000000010739a645 in ExecProcNode ()
#23 0x00000001073af88b in ExecNestLoop ()
#24 0x000000010739a645 in ExecProcNode ()
#25 0x00000001073af88b in ExecNestLoop ()
#26 0x000000010739a645 in ExecProcNode ()
#27 0x00000001073af88b in ExecNestLoop ()
#28 0x000000010739a645 in ExecProcNode ()
#29 0x00000001073af88b in ExecNestLoop ()
#30 0x000000010739a645 in ExecProcNode ()
#31 0x00000001073af88b in ExecNestLoop ()
#32 0x000000010739a645 in ExecProcNode ()
#33 0x00000001073af88b in ExecNestLoop ()
#34 0x000000010739a645 in ExecProcNode ()
#35 0x00000001073af88b in ExecNestLoop ()
#36 0x000000010739a645 in ExecProcNode ()
#37 0x00000001073af88b in ExecNestLoop ()
#38 0x000000010739a645 in ExecProcNode ()
#39 0x00000001073af929 in ExecNestLoop ()
#40 0x000000010739a645 in ExecProcNode ()
#41 0x000000010739816f in standard_ExecutorRun ()
#42 0x0000000107475b59 in PortalRunSelect ()
#43 0x000000010747574f in PortalRun ()
#44 0x0000000107473c83 in PostgresMain ()
#45 0x0000000107422a01 in PostmasterMain ()
#46 0x00000001073c8326 in main ()

Description of all tables used in the query:

pdb4=> \d tags_point;
Table "public.tags_point"
Column | Type | Modifiers
--------+-----------------------------+-----------
point | integer | not null
rev | bigint | not null
time | timestamp without time zone | not null

pdb4=> \d "pdb4_ad"."account_name"
Table "pdb4_ad.account_name"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
_value | text | not null
nid | bigint | not null
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
account | bigint |
Indexes:
"account_name__r_from_idx" btree (_r_from)
"account_name__r_to_idx" btree (_r_to)
"account_name__start_idx" btree (_start)
"account_name__stop_idx" btree (_stop)
"account_name_account_idx" btree (account)
"account_name_nid_idx" btree (nid)
Foreign-key constraints:
"account_name_account_fkey" FOREIGN KEY (account) REFERENCES pdb4_ad.account__id(nid)
"account_name_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_ad.account_name__id(nid)
Triggers:
pdb4_ad_trg__account_name_insert_id BEFORE INSERT ON pdb4_ad.account_name FOR EACH ROW EXECUTE PROCEDURE pdb4_ad.account_name__insert_trigger()

pdb4=> \d "pdb4_ad"."identifies"
Table "pdb4_ad.identifies"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
person | bigint |
account | bigint |
Indexes:
"identifies__r_from_idx" btree (_r_from)
"identifies__r_to_idx" btree (_r_to)
"identifies__start_idx" btree (_start)
"identifies__stop_idx" btree (_stop)
"identifies_account_idx" btree (account)
"identifies_nid_idx" btree (nid)
"identifies_person_idx" btree (person)
Foreign-key constraints:
"identifies_account_fkey" FOREIGN KEY (account) REFERENCES pdb4_ad.account__id(nid)
"identifies_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_ad.identifies__id(nid)
"identifies_person_fkey" FOREIGN KEY (person) REFERENCES pdb4_person.person__id(nid)
Triggers:
pdb4_ad_trg__identifies_insert_id BEFORE INSERT ON pdb4_ad.identifies FOR EACH ROW EXECUTE PROCEDURE pdb4_ad.identifies__insert_trigger()

pdb4=> \d "pdb4_person"."person"
Table "pdb4_person.person"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
name | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"person__r_from_idx" btree (_r_from)
"person__r_to_idx" btree (_r_to)
"person__start_idx" btree (_start)
"person__stop_idx" btree (_stop)
"person_name_idx" btree (name)
"person_nid_idx" btree (nid)
Foreign-key constraints:
"person_name_fkey" FOREIGN KEY (name) REFERENCES pdb4_person.person_name__id(nid)
"person_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_person.person__id(nid)
Triggers:
pdb4_person_trg__person_insert_id BEFORE INSERT ON pdb4_person.person FOR EACH ROW EXECUTE PROCEDURE pdb4_person.person__insert_trigger()

pdb4=> \d "pdb4_person"."person"
Table "pdb4_person.person"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
name | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"person__r_from_idx" btree (_r_from)
"person__r_to_idx" btree (_r_to)
"person__start_idx" btree (_start)
"person__stop_idx" btree (_stop)
"person_name_idx" btree (name)
"person_nid_idx" btree (nid)
Foreign-key constraints:
"person_name_fkey" FOREIGN KEY (name) REFERENCES pdb4_person.person_name__id(nid)
"person_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_person.person__id(nid)
Triggers:
pdb4_person_trg__person_insert_id BEFORE INSERT ON pdb4_person.person FOR EACH ROW EXECUTE PROCEDURE pdb4_person.person__insert_trigger()

pdb4=> \d "pdb4_org"."assignment"
Table "pdb4_org.assignment"
Column | Type | Modifiers
----------+-----------------------------+-----------
_r_from | bigint | not null
orgunit | bigint |
nid | bigint | not null
person | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
role | bigint |
owned_by | bigint |
Indexes:
"assignment__r_from_idx" btree (_r_from)
"assignment__r_to_idx" btree (_r_to)
"assignment__start_idx" btree (_start)
"assignment__stop_idx" btree (_stop)
"assignment_nid_idx" btree (nid)
"assignment_orgunit_idx" btree (orgunit)
"assignment_owned_by_idx" btree (owned_by)
"assignment_person_idx" btree (person)
"assignment_role_idx" btree (role)
Foreign-key constraints:
"assignment_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.assignment__id(nid)
"assignment_orgunit_fkey" FOREIGN KEY (orgunit) REFERENCES pdb4_org.orgunit__id(nid)
"assignment_owned_by_fkey" FOREIGN KEY (owned_by) REFERENCES pdb4_ad.service__id(nid)
"assignment_person_fkey" FOREIGN KEY (person) REFERENCES pdb4_person.person__id(nid)
"assignment_role_fkey" FOREIGN KEY (role) REFERENCES pdb4_org.role__id(nid)
Triggers:
pdb4_org_trg__assignment_insert_id BEFORE INSERT ON pdb4_org.assignment FOR EACH ROW EXECUTE PROCEDURE pdb4_org.assignment__insert_trigger()

pdb4=> \d "pdb4_org"."assignment_active"
Table "pdb4_org.assignment_active"
Column | Type | Modifiers
------------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
assignment | bigint |
Indexes:
"assignment_active__r_from_idx" btree (_r_from)
"assignment_active__r_to_idx" btree (_r_to)
"assignment_active__start_idx" btree (_start)
"assignment_active__stop_idx" btree (_stop)
"assignment_active_assignment_idx" btree (assignment)
"assignment_active_nid_idx" btree (nid)
Foreign-key constraints:
"assignment_active_assignment_fkey" FOREIGN KEY (assignment) REFERENCES pdb4_org.assignment__id(nid)
"assignment_active_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.assignment_active__id(nid)
Triggers:
pdb4_org_trg__assignment_active_insert_id BEFORE INSERT ON pdb4_org.assignment_active FOR EACH ROW EXECUTE PROCEDURE pdb4_org.assignment_active__insert_trigger()

pdb4=> \d "pdb4_org"."role"
Table "pdb4_org.role"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
name | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"role__r_from_idx" btree (_r_from)
"role__r_to_idx" btree (_r_to)
"role__start_idx" btree (_start)
"role__stop_idx" btree (_stop)
"role_name_idx" btree (name)
"role_nid_idx" btree (nid)
Foreign-key constraints:
"role_name_fkey" FOREIGN KEY (name) REFERENCES pdb4_org.role_name__id(nid)
"role_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.role__id(nid)
Triggers:
pdb4_org_trg__role_insert_id BEFORE INSERT ON pdb4_org.role FOR EACH ROW EXECUTE PROCEDURE pdb4_org.role__insert_trigger()

pdb4=> \d "pdb4_org"."role_name"
Table "pdb4_org.role_name"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
_value | text | not null
nid | bigint | not null
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"role_name__r_from_idx" btree (_r_from)
"role_name__r_to_idx" btree (_r_to)
"role_name__start_idx" btree (_start)
"role_name__stop_idx" btree (_stop)
"role_name_nid_idx" btree (nid)
Foreign-key constraints:
"role_name_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.role_name__id(nid)
Triggers:
pdb4_org_trg__role_name_insert_id BEFORE INSERT ON pdb4_org.role_name FOR EACH ROW EXECUTE PROCEDURE pdb4_org.role_name__insert_trigger()

pdb4=> \d "pdb4_org"."orgunit"
Table "pdb4_org.orgunit"
Column | Type | Modifiers
---------+-----------------------------+-----------
_r_from | bigint | not null
nid | bigint | not null
name | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"orgunit__r_from_idx" btree (_r_from)
"orgunit__r_to_idx" btree (_r_to)
"orgunit__start_idx" btree (_start)
"orgunit__stop_idx" btree (_stop)
"orgunit_name_idx" btree (name)
"orgunit_nid_idx" btree (nid)
Foreign-key constraints:
"orgunit_name_fkey" FOREIGN KEY (name) REFERENCES pdb4_org.orgunit_name__id(nid)
"orgunit_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.orgunit__id(nid)
Triggers:
pdb4_org_trg__orgunit_insert_id BEFORE INSERT ON pdb4_org.orgunit FOR EACH ROW EXECUTE PROCEDURE pdb4_org.orgunit__insert_trigger()

pdb4=> \d "pdb4_org"."orgunit_path"
Table "pdb4_org.orgunit_path"
Column | Type | Modifiers
---------+-----------------------------+-----------
type | bigint |
_r_from | bigint | not null
_value | integer | not null
nid | bigint | not null
end | bigint |
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
start | bigint |
Indexes:
"orgunit_path__r_from_idx" btree (_r_from)
"orgunit_path__r_to_idx" btree (_r_to)
"orgunit_path__start_idx" btree (_start)
"orgunit_path__stop_idx" btree (_stop)
"orgunit_path_end_idx" btree ("end")
"orgunit_path_nid_idx" btree (nid)
"orgunit_path_start_idx" btree (start)
"orgunit_path_type_idx" btree (type)
Foreign-key constraints:
"orgunit_path_end_fkey" FOREIGN KEY ("end") REFERENCES pdb4_org.orgunit__id(nid)
"orgunit_path_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.orgunit_path__id(nid)
"orgunit_path_start_fkey" FOREIGN KEY (start) REFERENCES pdb4_org.orgunit__id(nid)
"orgunit_path_type_fkey" FOREIGN KEY (type) REFERENCES pdb4_org.orgunit_link_type__id(nid)
Triggers:
pdb4_org_trg__orgunit_path_insert_id BEFORE INSERT ON pdb4_org.orgunit_path FOR EACH ROW EXECUTE PROCEDURE pdb4_org.orgunit_path__insert_trigger()

pdb4=> \d "pdb4_org"."orgunit_path_validity"
Table "pdb4_org.orgunit_path_validity"
Column | Type | Modifiers
---------+-----------------------------+-----------
path | bigint |
_r_from | bigint | not null
nid | bigint | not null
_stop | timestamp without time zone | not null
_start | timestamp without time zone | not null
_r_to | bigint |
Indexes:
"orgunit_path_validity__r_from_idx" btree (_r_from)
"orgunit_path_validity__r_to_idx" btree (_r_to)
"orgunit_path_validity__start_idx" btree (_start)
"orgunit_path_validity__stop_idx" btree (_stop)
"orgunit_path_validity_nid_idx" btree (nid)
"orgunit_path_validity_path_idx" btree (path)
Foreign-key constraints:
"orgunit_path_validity_nid_fkey" FOREIGN KEY (nid) REFERENCES pdb4_org.orgunit_path_validity__id(nid)
"orgunit_path_validity_path_fkey" FOREIGN KEY (path) REFERENCES pdb4_org.orgunit_path__id(nid)
Triggers:
pdb4_org_trg__orgunit_path_validity_insert_id BEFORE INSERT ON pdb4_org.orgunit_path_validity FOR EACH ROW EXECUTE PROCEDURE pdb4_org.orgunit_path_validity__insert_trigger()

And the size of each table:

pdb4=> select count(*) from tags_point;
count
-------
1
(1 row)

pdb4=> select count(*) from "pdb4_ad"."account_name";
count
--------
115054
(1 row)

pdb4=> select count(*) from "pdb4_ad"."identifies";
count
--------
112211
(1 row)

pdb4=> select count(*) from "pdb4_person"."person";
count
--------
112211
(1 row)

pdb4=> select count(*) from "pdb4_org"."assignment";
count
-------
0
(1 row)

pdb4=> select count(*) from "pdb4_org"."assignment_active";
count
-------
0
(1 row)

pdb4=> select count(*) from "pdb4_org"."role";
count
-------
8
(1 row)

pdb4=> select count(*) from "pdb4_org"."role_name";
count
-------
8
(1 row)

pdb4=> select count(*) from "pdb4_org"."orgunit";
count
-------
282
(1 row)

pdb4=> select count(*) from "pdb4_org"."orgunit_path";
count
-------
464
(1 row)

pdb4=> select count(*) from "pdb4_org"."orgunit_path_validity";
count
-------
464
(1 row)

В списке pgsql-general по дате отправления:

Предыдущее

От: basti
Дата: 27 апреля 2017 г., 19:08:03
Сообщение: [GENERAL] Postgres 9.6 Parallel Query

Следующее

От: pinker
Дата: 27 апреля 2017 г., 19:54:49
Сообщение: Re: [GENERAL] Query which "never" terminates

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

[GENERAL] Query which "never" terminates

Предыдущее

Следующее