Not so simple query and a half million loop
| От | Daniel Cristian Cruz |
|---|---|
| Тема | Not so simple query and a half million loop |
| Дата | |
| Msg-id | 48d0cacb0812010526i7c51b29fs9319618b3aaf3f49@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Not so simple query and a half million loop
|
| Список | pgsql-admin |
I've tryied 4 times to post this message to pgsql-performance without
success... No return, even an error...
Below is my problem; a query that perform bad when using a filter almost equal.
The problem (8.2.11):
EXPLAIN ANALYZE
SELECT
resource,
category,
userid,
title,
year,
month,
SUM(hours)
FROM
(
SELECT
r.name AS resource,
ARRAY(SELECT ca.cat_name FROM egw_categories ca WHERE
ca.cat_id::text = ANY (string_to_array(c.cal_category, ','))) AS
category,
cu.cal_user_id AS userid,
c.cal_title AS title,
EXTRACT(year FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start *
INTERVAL '1 second')) AS year,
EXTRACT(month FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start
* INTERVAL '1 second')) AS month,
(cd.cal_end - cd.cal_start) * INTERVAL '1 second' AS hours
FROM egw_cal_user cr
JOIN egw_cal_dates cd
ON
cd.cal_id = cr.cal_id AND
cr.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
JOIN egw_resources r
ON
r.res_id = cr.cal_user_id AND
cr.cal_user_type = 'r'
JOIN egw_categories ct
ON ct.cat_id = r.cat_id
JOIN egw_cal c
ON c.cal_id = cd.cal_id
LEFT JOIN egw_cal_user cu
ON
cu.cal_id = cr.cal_id AND
cu.cal_user_type = 'u' AND
cu.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
WHERE
ct.cat_main = 133 AND
r.res_id = 8522 AND
cu.cal_user_id = 278827 AND
EXTRACT(year FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) = 2008 AND
EXTRACT(month FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) IN (10,11,12)
) foo
GROUP BY
resource,
category,
userid,
title,
year,
month
ORDER BY
year,
month,
resource,
category,
userid,
title;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=125.20..183.67 rows=1 width=180) (actual
time=475276.902..475277.130 rows=4 loops=1)
-> Sort (cost=125.20..125.20 rows=1 width=180) (actual
time=475276.822..475276.920 rows=64 loops=1)
Sort Key: date_part('year'::text, ('1969-12-31
21:00:00-03'::timestamp with time zone + ((cd.cal_start)::double
precision * '00:00:01'::interval))), date_part('month'::text,
('1969-12-31 21:00:00-03'::timestamp with time zone +
((cd.cal_start)::double precision * '00:00:01'::interval))), r.name,
(subplan), cu.cal_user_id, c.cal_title
-> Nested Loop (cost=0.00..125.19 rows=1 width=180) (actual
time=22188.889..475275.364 rows=64 loops=1)
Join Filter: ((cr.cal_recur_date = COALESCE((subplan),
0::bigint)) AND (cu.cal_recur_date = COALESCE((subplan), 0::bigint)))
-> Nested Loop (cost=0.00..42.00 rows=1 width=192)
(actual time=0.535..2788.339 rows=511222 loops=1)
-> Nested Loop (cost=0.00..38.71 rows=1
width=171) (actual time=0.121..90.021 rows=1105 loops=1)
-> Nested Loop (cost=0.00..35.84 rows=1
width=120) (actual time=0.104..18.855 rows=1105 loops=1)
-> Nested Loop (cost=0.00..16.55
rows=1 width=112) (actual time=0.046..0.058 rows=1 loops=1)
-> Index Scan using
egw_resources_pkey on egw_resources r (cost=0.00..8.27 rows=1
width=116) (actual time=0.025..0.029 rows=1 loops=1)
Index Cond: (res_id = 8522)
-> Index Scan using
egw_categories_pkey on egw_categories ct (cost=0.00..8.27 rows=1
width=4) (actual time=0.012..0.015 rows=1 loops=1)
Index Cond: (ct.cat_id = r.cat_id)
Filter: (cat_main = 133)
-> Index Scan using idx_egw_0001 on
egw_cal_user cr (cost=0.00..19.23 rows=4 width=21) (actual
time=0.044..14.742 rows=1105 loops=1)
Index Cond: (((r.res_id)::text =
(cr.cal_user_id)::text) AND ((cr.cal_user_type)::text = 'r'::text))
-> Index Scan using egw_cal_pkey on egw_cal
c (cost=0.00..2.86 rows=1 width=51) (actual time=0.053..0.056 rows=1
loops=1105)
Index Cond: (cr.cal_id = c.cal_id)
-> Index Scan using egw_cal_user_pkey on
egw_cal_user cu (cost=0.00..3.28 rows=1 width=21) (actual
time=0.017..0.978 rows=463 loops=1105)
Index Cond: ((cu.cal_id = cr.cal_id) AND
((cu.cal_user_type)::text = 'u'::text) AND ((cu.cal_user_id)::text =
'278827'::text))
-> Index Scan using egw_cal_dates_pkey on egw_cal_dates
cd (cost=0.00..8.18 rows=1 width=20) (actual time=0.014..0.570
rows=30 loops=511222)
Index Cond: (cd.cal_id = cr.cal_id)
Filter: ((date_part('year'::text, ('1970-01-01
00:00:00'::timestamp without time zone + ((cal_start)::double
precision * '00:00:01'::interval))) = 2008::double precision) AND
(date_part('month'::text, ('1970-01-01 00:00:00'::timestamp without
time zone + ((cal_start)::double precision * '00:00:01'::interval))) =
ANY ('{10,11,12}'::double precision[])))
SubPlan
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.004..0.006 rows=1 loops=29520)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.005..0.006 rows=1 loops=15158976)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Bitmap Heap Scan on egw_categories ca
(cost=30.59..58.41 rows=10 width=50) (actual time=0.024..0.025 rows=1
loops=64)
Recheck Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
-> Bitmap Index Scan on idx_egw_0005
(cost=0.00..30.59 rows=10 width=0) (actual time=0.016..0.016 rows=1
loops=64)
Index Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
Total runtime: 475277.646 ms
(35 registros)
Good one:
EXPLAIN ANALYZE
SELECT
resource,
category,
userid,
title,
year,
month,
SUM(hours)
FROM
(
SELECT
r.name AS resource,
ARRAY(SELECT ca.cat_name FROM egw_categories ca WHERE
ca.cat_id::text = ANY (string_to_array(c.cal_category, ','))) AS
category,
cu.cal_user_id AS userid,
c.cal_title AS title,
EXTRACT(year FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start *
INTERVAL '1 second')) AS year,
EXTRACT(month FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start
* INTERVAL '1 second')) AS month,
(cd.cal_end - cd.cal_start) * INTERVAL '1 second' AS hours
FROM egw_cal_user cr
JOIN egw_cal_dates cd
ON
cd.cal_id = cr.cal_id AND
cr.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
JOIN egw_resources r
ON
r.res_id = cr.cal_user_id AND
cr.cal_user_type = 'r'
JOIN egw_categories ct
ON ct.cat_id = r.cat_id
JOIN egw_cal c
ON c.cal_id = cd.cal_id
LEFT JOIN egw_cal_user cu
ON
cu.cal_id = cr.cal_id AND
cu.cal_user_type = 'u' AND
cu.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
WHERE
ct.cat_main = 133 AND
cu.cal_user_id = 278827 AND
EXTRACT(year FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) = 2008 AND
EXTRACT(month FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) IN (10,11,12)
) foo
GROUP BY
resource,
category,
userid,
title,
year,
month
ORDER BY
year,
month,
resource,
category,
userid,
title;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=257.62..316.09 rows=1 width=180) (actual
time=135.885..136.112 rows=4 loops=1)
-> Sort (cost=257.62..257.62 rows=1 width=180) (actual
time=135.822..135.923 rows=64 loops=1)
Sort Key: date_part('year'::text, ('1969-12-31
21:00:00-03'::timestamp with time zone + ((cd.cal_start)::double
precision * '00:00:01'::interval))), date_part('month'::text,
('1969-12-31 21:00:00-03'::timestamp with time zone +
((cd.cal_start)::double precision * '00:00:01'::interval))), r.name,
(subplan), cu.cal_user_id, c.cal_title
-> Nested Loop (cost=40.72..257.61 rows=1 width=180) (actual
time=91.067..134.580 rows=64 loops=1)
-> Nested Loop (cost=40.72..190.89 rows=1 width=145)
(actual time=90.951..131.857 rows=64 loops=1)
-> Nested Loop (cost=32.44..166.02 rows=1
width=132) (actual time=33.022..127.492 rows=196 loops=1)
Join Filter: ((r.res_id)::text =
(cr.cal_user_id)::text)
-> Nested Loop (cost=32.44..110.02 rows=1
width=33) (actual time=32.927..82.418 rows=196 loops=1)
-> Bitmap Heap Scan on egw_cal_dates
cd (cost=24.16..35.42 rows=3 width=20) (actual time=3.834..10.337
rows=3871 loops=1)
Recheck Cond:
((date_part('year'::text, ('1970-01-01 00:00:00'::timestamp without
time zone + ((cal_start)::double precision * '00:00:01'::interval))) =
2008::double precision) AND (date_part('month'::text, ('1970-01-01
00:00:00'::timestamp without time zone + ((cal_start)::double
precision * '00:00:01'::interval))) = ANY ('{10,11,12}'::double
precision[])))
-> BitmapAnd
(cost=24.16..24.16 rows=3 width=0) (actual time=3.795..3.795 rows=0
loops=1)
-> Bitmap Index Scan on
idx_egw_0002 (cost=0.00..5.99 rows=230 width=0) (actual
time=1.972..1.972 rows=14118 loops=1)
Index Cond:
(date_part('year'::text, ('1970-01-01 00:00:00'::timestamp without
time zone + ((cal_start)::double precision * '00:00:01'::interval))) =
2008::double precision)
-> Bitmap Index Scan on
idx_egw_0006 (cost=0.00..17.92 rows=687 width=0) (actual
time=1.789..1.789 rows=12799 loops=1)
Index Cond:
(date_part('month'::text, ('1970-01-01 00:00:00'::timestamp without
time zone + ((cal_start)::double precision * '00:00:01'::interval))) =
ANY ('{10,11,12}'::double precision[]))
-> Index Scan using egw_cal_user_pkey
on egw_cal_user cr (cost=8.28..16.57 rows=1 width=21) (actual
time=0.005..0.005 rows=0 loops=3871)
Index Cond: ((cd.cal_id =
cr.cal_id) AND (cr.cal_recur_date = COALESCE((subplan), 0::bigint))
AND ((cr.cal_user_type)::text = 'r'::text))
SubPlan
-> Index Scan using
egw_cal_dates_pkey on egw_cal_dates icd (cost=0.00..8.28 rows=1
width=0) (actual time=0.005..0.006 rows=1 loops=3871)
Index Cond: ((cal_id =
$2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using
egw_cal_dates_pkey on egw_cal_dates icd (cost=0.00..8.28 rows=1
width=0) (never executed)
Index Cond: ((cal_id =
$2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Nested Loop (cost=0.00..55.80 rows=13
width=112) (actual time=0.013..0.197 rows=13 loops=196)
-> Index Scan using idx_egw_0003 on
egw_categories ct (cost=0.00..13.16 rows=3 width=4) (actual
time=0.004..0.032 rows=15 loops=196)
Index Cond: (cat_main = 133)
-> Index Scan using idx_egw_0004 on
egw_resources r (cost=0.00..14.08 rows=11 width=116) (actual
time=0.003..0.005 rows=1 loops=2940)
Index Cond: (ct.cat_id = r.cat_id)
-> Index Scan using egw_cal_user_pkey on
egw_cal_user cu (cost=8.28..16.58 rows=1 width=21) (actual
time=0.007..0.008 rows=0 loops=196)
Index Cond: ((cu.cal_id = cr.cal_id) AND
(cu.cal_recur_date = COALESCE((subplan), 0::bigint)) AND
((cu.cal_user_type)::text = 'u'::text) AND ((cu.cal_user_id)::text =
'278827'::text))
SubPlan
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.005..0.007 rows=1 loops=196)
Index Cond: ((cal_id = $2) AND
(cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (never executed)
Index Cond: ((cal_id = $2) AND
(cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_pkey on egw_cal c
(cost=0.00..8.27 rows=1 width=51) (actual time=0.004..0.006 rows=1
loops=64)
Index Cond: (c.cal_id = cd.cal_id)
SubPlan
-> Bitmap Heap Scan on egw_categories ca
(cost=30.59..58.41 rows=10 width=50) (actual time=0.013..0.015 rows=1
loops=64)
Recheck Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
-> Bitmap Index Scan on idx_egw_0005
(cost=0.00..30.59 rows=10 width=0) (actual time=0.008..0.008 rows=1
loops=64)
Index Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
Total runtime: 136.702 ms
(46 registros)
Another good one:
EXPLAIN ANALYZE
SELECT
resource,
category,
userid,
title,
year,
month,
SUM(hours)
FROM
(
SELECT
r.name AS resource,
ARRAY(SELECT ca.cat_name FROM egw_categories ca WHERE
ca.cat_id::text = ANY (string_to_array(c.cal_category, ','))) AS
category,
cu.cal_user_id AS userid,
c.cal_title AS title,
EXTRACT(year FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start *
INTERVAL '1 second')) AS year,
EXTRACT(month FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start
* INTERVAL '1 second')) AS month,
(cd.cal_end - cd.cal_start) * INTERVAL '1 second' AS hours
FROM egw_cal_user cr
JOIN egw_cal_dates cd
ON
cd.cal_id = cr.cal_id AND
cr.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
JOIN egw_resources r
ON
r.res_id = cr.cal_user_id AND
cr.cal_user_type = 'r'
JOIN egw_categories ct
ON ct.cat_id = r.cat_id
JOIN egw_cal c
ON c.cal_id = cd.cal_id
LEFT JOIN egw_cal_user cu
ON
cu.cal_id = cr.cal_id AND
cu.cal_user_type = 'u' AND
cu.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
WHERE
ct.cat_main = 133 AND
r.res_id = 8522 AND
EXTRACT(year FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) = 2008 AND
EXTRACT(month FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) IN (10,11,12)
) foo
GROUP BY
resource,
category,
userid,
title,
year,
month
ORDER BY
year,
month,
resource,
category,
userid,
title;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=132.17..190.64 rows=1 width=180) (actual
time=978.502..979.174 rows=18 loops=1)
-> Sort (cost=132.17..132.17 rows=1 width=180) (actual
time=978.448..978.686 rows=160 loops=1)
Sort Key: date_part('year'::text, ('1969-12-31
21:00:00-03'::timestamp with time zone + ((cd.cal_start)::double
precision * '00:00:01'::interval))), date_part('month'::text,
('1969-12-31 21:00:00-03'::timestamp with time zone +
((cd.cal_start)::double precision * '00:00:01'::interval))), r.name,
(subplan), cu.cal_user_id, c.cal_title
-> Nested Loop Left Join (cost=8.28..132.16 rows=1
width=180) (actual time=0.441..974.424 rows=160 loops=1)
-> Nested Loop (cost=0.00..48.86 rows=1 width=179)
(actual time=0.341..966.004 rows=149 loops=1)
Join Filter: (cr.cal_recur_date =
COALESCE((subplan), 0::bigint))
-> Nested Loop (cost=0.00..38.71 rows=1
width=171) (actual time=0.123..19.732 rows=1105 loops=1)
-> Nested Loop (cost=0.00..35.84 rows=1
width=120) (actual time=0.106..5.790 rows=1105 loops=1)
-> Nested Loop (cost=0.00..16.55
rows=1 width=112) (actual time=0.046..0.060 rows=1 loops=1)
-> Index Scan using
egw_resources_pkey on egw_resources r (cost=0.00..8.27 rows=1
width=116) (actual time=0.025..0.029 rows=1 loops=1)
Index Cond: (res_id = 8522)
-> Index Scan using
egw_categories_pkey on egw_categories ct (cost=0.00..8.27 rows=1
width=4) (actual time=0.012..0.016 rows=1 loops=1)
Index Cond: (ct.cat_id = r.cat_id)
Filter: (cat_main = 133)
-> Index Scan using idx_egw_0001 on
egw_cal_user cr (cost=0.00..19.23 rows=4 width=21) (actual
time=0.044..2.297 rows=1105 loops=1)
Index Cond: (((r.res_id)::text =
(cr.cal_user_id)::text) AND ((cr.cal_user_type)::text = 'r'::text))
-> Index Scan using egw_cal_pkey on egw_cal
c (cost=0.00..2.86 rows=1 width=51) (actual time=0.004..0.006 rows=1
loops=1105)
Index Cond: (c.cal_id = cr.cal_id)
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates cd (cost=0.00..1.86 rows=1 width=20) (actual
time=0.013..0.526 rows=28 loops=1105)
Index Cond: (cd.cal_id = cr.cal_id)
Filter: ((date_part('year'::text,
('1970-01-01 00:00:00'::timestamp without time zone +
((cal_start)::double precision * '00:00:01'::interval))) =
2008::double precision) AND (date_part('month'::text, ('1970-01-01
00:00:00'::timestamp without time zone + ((cal_start)::double
precision * '00:00:01'::interval))) = ANY ('{10,11,12}'::double
precision[])))
SubPlan
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.005..0.006 rows=1 loops=30929)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_user_pkey on egw_cal_user
cu (cost=8.28..16.57 rows=1 width=21) (actual time=0.007..0.009
rows=1 loops=149)
Index Cond: ((cu.cal_id = cr.cal_id) AND
(cu.cal_recur_date = COALESCE((subplan), 0::bigint)) AND
((cu.cal_user_type)::text = 'u'::text))
SubPlan
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.004..0.006 rows=1 loops=149)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (never executed)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
SubPlan
-> Bitmap Heap Scan on egw_categories ca
(cost=30.59..58.41 rows=10 width=50) (actual time=0.013..0.014 rows=1
loops=160)
Recheck Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
-> Bitmap Index Scan on idx_egw_0005
(cost=0.00..30.59 rows=10 width=0) (actual time=0.008..0.008 rows=1
loops=160)
Index Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
Total runtime: 979.685 ms
(40 registros)
Almost the same thing on 8.3.5 (another hardware):
EXPLAIN ANALYZE
SELECT
resource,
category,
userid,
title,
year,
month,
SUM(hours)
FROM
(
SELECT
r.name AS resource,
ARRAY(SELECT ca.cat_name FROM egw_categories ca WHERE
ca.cat_id::text = ANY (string_to_array(c.cal_category, ','))) AS
category,
cu.cal_user_id AS userid,
c.cal_title AS title,
EXTRACT(year FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start *
INTERVAL '1 second')) AS year,
EXTRACT(month FROM (TIMESTAMP WITH TIME ZONE 'epoch' + cd.cal_start
* INTERVAL '1 second')) AS month,
(cd.cal_end - cd.cal_start) * INTERVAL '1 second' AS hours
FROM egw_cal_user cr
JOIN egw_cal_dates cd
ON
cd.cal_id = cr.cal_id AND
cr.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
JOIN egw_resources r
ON
r.res_id::text = cr.cal_user_id AND
cr.cal_user_type = 'r'
JOIN egw_categories ct
ON ct.cat_id = r.cat_id
JOIN egw_cal c
ON c.cal_id = cd.cal_id
LEFT JOIN egw_cal_user cu
ON
cu.cal_id = cr.cal_id AND
cu.cal_user_type = 'u' AND
cu.cal_recur_date = COALESCE((SELECT cd.cal_start FROM
egw_cal_dates icd WHERE icd.cal_id = cd.cal_id AND icd.cal_start =
cd.cal_start AND icd.cal_start <> 0), 0)
WHERE
ct.cat_main = 133 AND
r.res_id = 8522 AND
cu.cal_user_id = '278827' AND
EXTRACT(year FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) = 2008 AND
EXTRACT(month FROM (TIMESTAMP 'epoch' + cd.cal_start * INTERVAL '1
second')) IN (10,11,12)
) foo
GROUP BY
resource,
category,
userid,
title,
year,
month
ORDER BY
year,
month,
resource,
category,
userid,
title;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=131.17..215.53 rows=1 width=175) (actual
time=3179750.197..3179750.747 rows=4 loops=1)
-> Sort (cost=131.17..131.18 rows=1 width=175) (actual
time=3179749.897..3179750.007 rows=64 loops=1)
Sort Key: (date_part('year'::text, ('1969-12-31
21:00:00-03'::timestamp with time zone + ((cd.cal_start)::double
precision * '00:00:01'::interval)))), (date_part('month'::text,
('1969-12-31 21:00:00-03'::timestamp with time zone +
((cd.cal_start)::double precision * '00:00:01'::interval)))), r.name,
((subplan)), cu.cal_user_id, c.cal_title
Sort Method: quicksort Memory: 33kB
-> Nested Loop (cost=0.01..131.16 rows=1 width=175) (actual
time=155178.467..3179744.010 rows=64 loops=1)
Join Filter: ((cr.cal_recur_date = COALESCE((subplan),
0::bigint)) AND (cu.cal_recur_date = COALESCE((subplan), 0::bigint)))
-> Nested Loop (cost=0.01..29.25 rows=1 width=187)
(actual time=0.274..55246.217 rows=511222 loops=1)
-> Nested Loop (cost=0.01..27.36 rows=1
width=140) (actual time=0.255..13234.191 rows=511222 loops=1)
-> Nested Loop (cost=0.01..24.85 rows=1
width=122) (actual time=0.226..126.224 rows=1093 loops=1)
-> Nested Loop (cost=0.01..16.57
rows=1 width=126) (actual time=0.192..20.013 rows=1093 loops=1)
-> Index Scan using
egw_resources_pkey on egw_resources r (cost=0.00..8.27 rows=1
width=118) (actual time=0.055..0.061 rows=1 loops=1)
Index Cond: (res_id = 8522)
-> Index Scan using
idx_egw_0001 on egw_cal_user cr (cost=0.01..8.28 rows=1 width=18)
(actual time=0.093..9.802 rows=1093 loops=1)
Index Cond:
(((cr.cal_user_id)::text = (r.res_id)::text) AND
((cr.cal_user_type)::text = 'r'::text))
-> Index Scan using
egw_categories_pkey on egw_categories ct (cost=0.00..8.27 rows=1
width=4) (actual time=0.064..0.077 rows=1 loops=1093)
Index Cond: (ct.cat_id = r.cat_id)
Filter: (ct.cat_main = 133)
-> Index Scan using egw_cal_user_pkey on
egw_cal_user cu (cost=0.00..2.49 rows=1 width=18) (actual
time=0.138..5.611 rows=468 loops=1093)
Index Cond: ((cu.cal_id = cr.cal_id)
AND ((cu.cal_user_type)::text = 'u'::text) AND ((cu.cal_user_id)::text
= '278827'::text))
-> Index Scan using egw_cal_pkey on egw_cal c
(cost=0.00..1.89 rows=1 width=47) (actual time=0.044..0.052 rows=1
loops=511222)
Index Cond: (c.cal_id = cr.cal_id)
-> Index Scan using egw_cal_dates_pkey on egw_cal_dates
cd (cost=0.00..1.02 rows=1 width=20) (actual time=0.108..4.281
rows=30 loops=511222)
Index Cond: (cd.cal_id = cr.cal_id)
Filter: ((date_part('year'::text, ('1970-01-01
00:00:00'::timestamp without time zone + ((cd.cal_start)::double
precision * '00:00:01'::interval))) = 2008::double precision) AND
(date_part('month'::text, ('1970-01-01 00:00:00'::timestamp without
time zone + ((cd.cal_start)::double precision *
'00:00:01'::interval))) = ANY ('{10,11,12}'::double precision[])))
SubPlan
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.029..0.035 rows=1 loops=29520)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Index Scan using egw_cal_dates_pkey on
egw_cal_dates icd (cost=0.00..8.28 rows=1 width=0) (actual
time=0.034..0.040 rows=1 loops=15158976)
Index Cond: ((cal_id = $2) AND (cal_start = $1))
Filter: (cal_start <> 0)
-> Bitmap Heap Scan on egw_categories ca
(cost=31.29..84.29 rows=100 width=48) (actual time=0.127..0.131 rows=1
loops=64)
Recheck Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
-> Bitmap Index Scan on idx_egw_0005
(cost=0.00..31.27 rows=100 width=0) (actual time=0.097..0.097 rows=1
loops=64)
Index Cond: ((cat_id)::text = ANY
(string_to_array(($0)::text, ','::text)))
Total runtime: 3179751.510 ms
(36 registros)
Any suggestions? Is it a BUG?
Regards,
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
В списке pgsql-admin по дате отправления:
Следующее
От: "Daniel Cristian Cruz"Дата:
Сообщение: Re: Not so simple query and a half million loop