Обсуждение: Not so simple query and a half million loop

Поиск
Список
Период
Сортировка

Not so simple query and a half million loop

От
"Daniel Cristian Cruz"
Дата:
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
クルズ  クリスチアン ダニエル

Re: Not so simple query and a half million loop

От
"Daniel Cristian Cruz"
Дата:
Maybe someone would like to see it without broken lines (I do).

Regards,

2008/12/1 Daniel Cristian Cruz <danielcristian@gmail.com>:
> 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.
>
> ...

--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル

Вложения

Re: Not so simple query and a half million loop

От
"Daniel Cristian Cruz"
Дата:
No one knows why I got "actual time=0.014..0.570 rows=30 loops=511222"
and "actual time=0.005..0.006 rows=1 loops=15158976"?

With:
 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)
 ~ 450 seconds

With
 cu.cal_user_type = 'u'
 ~ 130 ms

With
 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)
 ~ 1 second

I've tryied to increase statistics to maximum, with no success.

This is the eGroupware database schema, with a few records.

Regards,

2008/12/1 Daniel Cristian Cruz <danielcristian@gmail.com>:
> Maybe someone would like to see it without broken lines (I do).
>
> Regards,
>
> 2008/12/1 Daniel Cristian Cruz <danielcristian@gmail.com>:
>> 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.
>>
>> ...
>
> --
> Daniel Cristian Cruz
> クルズ  クリスチアン ダニエル
>



--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル

Re: Not so simple query and a half million loop

От
Alvaro Herrera
Дата:
Daniel Cristian Cruz escribió:
> No one knows why I got "actual time=0.014..0.570 rows=30 loops=511222"
> and "actual time=0.005..0.006 rows=1 loops=15158976"?
>
> With:
>  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)
>  ~ 450 seconds

I don't know, but why are you using such an expression instead of an
outer join?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Not so simple query and a half million loop

От
DANIEL CRISTIAN CRUZ
Дата:
Because of a Bad Model...

When it is a single event egw_cal_dates always has a record with cal_start =
0.

When it is a recurrent event, egw_cal_dates has a record with cal_start = 0
and a record with cal_start equal to recurrent start.

With this construction it returns correctly calendar exceptions...

It's somewhat complex but it returns slowly ok.

Regards,

"Alvaro Herrera" <alvherre@commandprompt.com> escreveu:
> Daniel Cristian Cruz escribió:
>> No one knows why I got "actual time=0.014..0.570 rows=30 loops=511222"
>> and "actual time=0.005..0.006 rows=1 loops=15158976"?
>>
>> With:
>>  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)
>>  ~ 450 seconds
>
> I don't know, but why are you using such an expression instead of an
> outer join?
>
> --
> Alvaro Herrera
http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
<span style="color: #000080">Daniel Cristian Cruz
</span>Administrador de Banco de Dados
Direção Regional - Núcleo de Tecnologia da Informação
SENAI - SC
Telefone: 48-3239-1422 (ramal 1422)