Same postgres query producing different query plans in different databases

Question

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

Answer 1

I was facing the same issue, in the "backup" database a query was blazing fast, and the same query when run in the production database was horribly slow. It turns out I only needed to perform Routine Database Maintenance Tasks more often to solve the problem:

The PostgreSQL query planner relies on statistical information about the contents of tables in order to generate good plans for queries. These statistics are gathered by the ANALYZE command, which can be invoked by itself or as an optional step in VACUUM. It is important to have reasonably accurate statistics, otherwise poor choices of plans may degrade database performance.

So if you're experiencing a similar issue try running the following command on your "slow" database:

$ vacuumdb --host localhost --port 5432 --username "MyUser" -d "MyDatabase" --analyze --verbose