posgtres version 9.1.9
Following query produces different plan when run in two different databases.
explain (analyze,buffers) SELECT group_.groupid AS groupId,
group_.name AS groupName,
group_.type_ AS groupType,
group_.friendlyurl AS groupFriendlyURL
FROM group_
inner join groups_orgs
ON ( groups_orgs.groupid = group_.groupid )
inner join users_orgs
ON ( users_orgs.organizationid = groups_orgs.organizationid )
WHERE ( group_.livegroupid = 0 )
AND ( users_orgs.userid = '27091470' )
AND ( group_.companyid = '20002' )
AND ( group_.classnameid = 10001
OR group_.classnameid = 10003 )
AND ( group_.name != 'Control Panel' )
AND ( group_.type_ != 4 )
;
Plan from Production database.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=6.36..16.60 rows=1 width=37) (actual time=0.133..95.323 rows=3 loops=1)
Merge Cond: (group_.groupid = groups_orgs.groupid)
Buffers: shared hit=30829
-> Index Scan using group__pkey on group_ (cost=0.00..87997.62 rows=17244 width=37) (actual time=0.030..85.166 rows=13906 loops=1)
Filter: (((name)::text <> 'Control Panel'::text) AND (type_ <> 4) AND (livegroupid = 0) AND (companyid = 20002::bigint) AND ((classnameid = 10001) OR (classnameid = 10003)))
Buffers: shared hit=30824
-> Sort (cost=6.36..6.37 rows=3 width=8) (actual time=0.076..0.079 rows=3 loops=1)
Sort Key: groups_orgs.groupid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=5
-> Merge Join (cost=1.05..6.34 rows=3 width=8) (actual time=0.045..0.054 rows=3 loops=1)
Merge Cond: (users_orgs.organizationid = groups_orgs.organizationid)
Buffers: shared hit=5
-> Index Scan using users_orgs_pkey on users_orgs (cost=0.00..10.47 rows=2 width=8) (actual time=0.012..0.014 rows=2 loops=1)
Index Cond: (userid = 27091470::bigint)
Buffers: shared hit=4
-> Sort (cost=1.05..1.06 rows=3 width=16) (actual time=0.028..0.030 rows=3 loops=1)
Sort Key: groups_orgs.organizationid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Seq Scan on groups_orgs (cost=0.00..1.03 rows=3 width=16) (actual time=0.003..0.005 rows=3 loops=1)
Buffers: shared hit=1
Plan from database which is created by exporting/importing data from production
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..18.19 rows=1 width=36) (actual time=0.053..0.104 rows=3 loops=1)
Buffers: shared hit=18
-> Nested Loop (cost=0.00..9.77 rows=1 width=8) (actual time=0.036..0.065 rows=3 loops=1)
Join Filter: (groups_orgs.organizationid = users_orgs.organizationid)
Buffers: shared hit=6
-> Seq Scan on groups_orgs (cost=0.00..1.03 rows=3 width=16) (actual time=0.007..0.010 rows=3 loops=1)
Buffers: shared hit=1
-> Materialize (cost=0.00..8.66 rows=2 width=8) (actual time=0.008..0.012 rows=3 loops=3)
Buffers: shared hit=5
-> Index Scan using ix_fb646ca6 on users_orgs (cost=0.00..8.65 rows=2 width=8) (actual time=0.016..0.021 rows=3 loops=1)
Index Cond: (userid = 27091470::bigint)
Buffers: shared hit=5
-> Index Scan using group__pkey on group_ (cost=0.00..8.41 rows=1 width=36) (actual time=0.008..0.010 rows=1 loops=3)
Index Cond: (groupid = groups_orgs.groupid)
Filter: (((name)::text <> 'Control Panel'::text) AND (type_ <> 4) AND (livegroupid = 0) AND (companyid = 20002::bigint) AND ((classnameid = 10001) OR (classnameid = 10003)))
Buffers: shared hit=12
Production query takes around 100ms and in other DB takes 0.1ms
Difference seems to be slow index scan on group_ table (Index Scan using group__pkey on group_)
Can anyone explain the difference in execution time?
Tables in production are regularly vacuumed and analyzed.
Production DB is more busy than other DB.
Thanks, Sameer
