Sorry,
just noticed that both query plans are actually for the 8.0.13 release.
This one is right: "Total runtime: 93866.526 ms"
Any suggestions? Please.
Aleksandr Vinokurov wrote:
Both plans with queries echoed are attached to this email.
Timing is on.
explain analyze
select log_rec_id,
log_date,
log_uid,
log_name,
array_accum(trim(trailing ' ' from gh_name)) as groups
from (
select distinct on (log_rec_id, start_rec_id, fin_rec_id, gm_rec_id)
log_rec_id,
log_date,
log_uid,
log_name,
start_rec_id,
start_date,
start_action,
start_uid,
start_name,
fin_rec_id,
fin_date,
fin_action,
fin_uid,
fin_name,
gm_rec_id,
gm_date,
gm_gid,
gm_uid,
gh.rec_id as gh_rec_id,
gh.date as gh_date,
gh.action as gh_action,
gh.gid as gh_gid,
gh.name as gh_name
from (
select *
from group_history
where action <> 1
) as gh
right join (
select log_rec_id,
log_date,
log_uid,
log_name,
start_rec_id,
start_date,
start_action,
start_uid,
start_name,
fin_rec_id,
fin_date,
fin_action,
fin_uid,
fin_name,
max(gm_rec_id) as gm_rec_id,
max(gm_date) as gm_date,
gm_gid,
gm_uid
from (
select
luid.*,
gmh.rec_id as gm_rec_id,
gmh.date as gm_date,
gmh.gid as gm_gid,
gmh.uid as gm_uid,
gmh.action as gm_action
from group_member_history as gmh
right join (
select distinct on (cuh.log_rec_id, cuh.start_rec_id)
cuh.*, duh.*
from (
select rec_id as fin_rec_id,
date as fin_date,
action as fin_action,
uid as fin_uid,
name as fin_name
from "user_history"
where 1 = 1
and action <> 0
) as duh
right join (
select distinct on (log.rec_id)
log.rec_id as log_rec_id,
log.date as log_date,
log.uid as log_uid,
log.name as log_name,
uh.rec_id as start_rec_id,
uh.date as start_date,
uh.action as start_action,
uh.uid as start_uid,
uh.name as start_name
from (
select *
from "user_history"
where 1 = 1
and action <> 1
order by date
) as uh
right join log_example_3 as log
on log.name = uh.name
and uh.date <= log.date
order by log.rec_id, start_date desc
) as cuh
on cuh.start_uid = duh.fin_uid
and duh.fin_date > cuh.start_date and duh.fin_date <=
cuh.log_date
order by cuh.log_rec_id, cuh.start_rec_id, duh.fin_rec_id
) as luid
on gmh.uid = luid.start_uid
and gmh.date <= luid.log_date
) as lgm
group by
log_rec_id,
log_date,
log_uid,
log_name,
start_rec_id,
start_date,
start_action,
start_uid,
start_name,
fin_rec_id,
fin_date,
fin_action,
fin_uid,
fin_name,
gm_gid,
gm_uid
having count(gm_action) % 2 = 1 or count(gm_action) = 0
order by log_date
) as lgm
on gh.gid = lgm.gm_gid
and gh.date <= lgm.log_date
order by log_rec_id, start_rec_id, fin_rec_id, gm_rec_id, gh.date desc
) as lgs
group by log_rec_id,
log_date,
log_uid,
log_name
order by log_date;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=114905.79..114905.79 rows=1 width=44) (actual
time=93817.815..93817.870 rows=99 loops=1)
Sort Key: log_date
-> HashAggregate (cost=114905.77..114905.78 rows=1 width=44) (actual
time=93817.110..93817.450 rows=99 loops=1)
-> Subquery Scan lgs (cost=114888.70..114905.69 rows=6 width=44)
(actual time=93704.252..93812.964 rows=439 loops=1)
-> Unique (cost=114888.70..114905.63 rows=6 width=146) (actual
time=93704.229..93811.067 rows=439 loops=1)
-> Sort (cost=114888.70..114892.08 rows=1355 width=146)
(actual time=93704.223..93749.037 rows=26139 loops=1)
Sort Key: lgm.log_rec_id, lgm.start_rec_id,
lgm.fin_rec_id, lgm.gm_rec_id, group_history.date
-> Nested Loop Left Join
(cost=112179.18..114818.21 rows=1355 width=146) (actual
time=33848.546..92399.306 rows=26139 loops=1)
-> Subquery Scan lgm
(cost=112179.18..112179.88 rows=56 width=114) (actual time=33833.584..33853.948
rows=439 loops=1)
-> Sort (cost=112179.18..112179.32
rows=56 width=116) (actual time=33833.546..33834.852 rows=439 loops=1)
Sort Key: luid.log_date
-> HashAggregate
(cost=112176.57..112177.55 rows=56 width=116) (actual time=33826.827..33831.630
rows=439 loops=1)
Filter: (((count("action") %
2::bigint) = 1) OR (count("action") = 0))
-> Nested Loop Left Join
(cost=112022.13..112173.77 rows=56 width=116) (actual time=20817.795..33804.325
rows=649 loops=1)
-> Subquery Scan luid
(cost=112022.13..112028.17 rows=11 width=94) (actual time=20743.169..20748.324
rows=100 loops=1)
-> Unique
(cost=112022.13..112028.06 rows=11 width=94) (actual time=20743.147..20744.932
rows=100 loops=1)
-> Sort
(cost=112022.13..112024.11 rows=790 width=94) (actual time=20743.144..20743.426
rows=100 loops=1)
Sort
Key: cuh.log_rec_id, cuh.start_rec_id, user_history.rec_id
->
Nested Loop Left Join (cost=109926.12..111984.11 rows=790 width=94) (actual
time=20709.280..20742.897 rows=100 loops=1)
-> Subquery Scan cuh (cost=109926.12..110297.49 rows=100 width=62) (actual
time=20708.952..20737.891 rows=100 loops=1)
-> Unique (cost=109926.12..110296.49 rows=100 width=62) (actual
time=20708.920..20737.145 rows=100 loops=1)
-> Sort (cost=109926.12..110111.30 rows=74075 width=62) (actual
time=20708.914..20727.984 rows=13370 loops=1)
Sort Key: log.rec_id, uh.date
-> Merge Left Join (cost=96406.26..101406.78 rows=74075
width=62) (actual time=19252.554..20523.253 rows=13370 loops=1)
Merge Cond: ("outer".name = "inner".name)
Join Filter: ("inner".date <= "outer".date)
-> Sort (cost=5.32..5.57 rows=100 width=30) (actual
time=0.869..0.948 rows=100 loops=1)
Sort Key: log.name
-> Seq Scan on log_example_3 log
(cost=0.00..2.00 rows=100 width=30) (actual time=0.112..0.245 rows=100 loops=1)
-> Sort (cost=96400.94..97512.05 rows=444446
width=32) (actual time=19251.556..19819.983 rows=442847 loops=1)
Sort Key: uh.name
-> Subquery Scan uh (cost=0.00..25148.24
rows=444446 width=32) (actual time=0.353..3422.179 rows=442203 loops=1)
-> Index Scan using
indx_date_action02_user_history on user_history (cost=0.00..20703.78
rows=444446 width=32) (actual time=0.326..2018.154 rows=442203 loops=1)
Filter: ("action" <> 1)
-> Index Scan using indx_date_action12_uid_user_his on user_history
(cost=0.00..16.73 rows=8 width=32) (actual time=0.038..0.038 rows=0 loops=100)
Index Cond: ((user_history.date > "outer".start_date) AND
(user_history.date <= "outer".log_date) AND ("outer".start_uid =
user_history.uid))
Filter: ("action" <> 0)
-> Index Scan using
indx_date_uid_on_group_member_h on group_member_history gmh (cost=0.00..13.15
rows=6 width=22) (actual time=80.107..130.251 rows=6 loops=100)
Index Cond:
((gmh.date <= "outer".log_date) AND (gmh.uid = "outer".start_uid))
-> Index Scan using
indx_date_action02_gid_group_hist on group_history (cost=0.00..46.74 rows=25
width=32) (actual time=16.099..131.682 rows=59 loops=439)
Index Cond: ((group_history.date <=
"outer".log_date) AND (group_history.gid = "outer".gm_gid))
Filter: ("action" <> 1)
Total runtime: 93866.526 ms
(43 rows)
Time: 94083.803 ms
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
