Date: Fri, 13 Sep 2019 18:06:15 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Amarendra Konda <amar.vijaya@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query execution time Vs Cost
Message-ID: <20190913230615.GB22849@telsasoft.com>
References: 
 <CAJNAD0=+N7_EAUjUucndnSrgsWXPXP+ScS+6XzcLVv5F=gaQCg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAJNAD0=+N7_EAUjUucndnSrgsWXPXP+ScS+6XzcLVv5F=gaQCg@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Fri, Sep 13, 2019 at 04:38:50PM +0530, Amarendra Konda wrote:
> As part of one query tuning, it was observed that query execution time was
> more even though cost was decreased.

..

> May i know the reason behind in increase in response time, even though cost
> was reduced by 6.4 times.

The "cost" is postgres model for how expensive a plan will be, based on table
statistics, and parameters like seq/rand_page_cost, etc.  It's an imperfect
model and not exact.

> *Initial Query*
> 
> => explain(analyze,buffers,costs)  SELECT    ku.user_id
> >     FROM      konotor_user ku
> >  LEFT JOIN agent_details ad
> >  ON        ku.user_id = ad.user_id
> >  WHERE     ku.app_id = '12132818272260'
> >  AND       (ku.user_type = 1 OR ku.user_type = 2)
> >  AND       (ad.deleted isnull OR ad.deleted = 0)
> >  AND       ku.user_id NOT IN (
> >                        SELECT     gu.user_id
> >                        FROM       group_user gu
> >                        INNER JOIN groups
> >                        ON         gu.group_id = groups.group_id
> >                        AND        app_id = ku.app_id
> >                        WHERE      gu.user_id = ku.user_id
> >                        AND        groups.app_id = ku.app_id
> >                        AND        groups.deleted = false);

It seems to me the major difference is in group_user JOIN groups.

In the fast query, it did
>                  ->  Index Only Scan using uk_groupid_userid on group_user gu  (cost=0.29..8.30 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=15832)
>                        Index Cond: ((group_id = groups.group_id) AND (user_id = ku.user_id))
>                        Heap Fetches: 455
>                        Buffers: shared hit=32210

=> 15832*0.001sec = 15ms 

In the slow query it did:
>          ->  Index Only Scan using uk_groupid_userid on group_user gu (cost=0.29..115.12 rows=2 width=16) (actual time=0.135..0.135 rows=1 loops=785)
>                Index Cond: (user_id = ku.user_id)
>                Heap Fetches: 456
>                Buffers: shared hit=45529

=> 785*0.115sec = 90ms

It scanned using non-leading columns of index, so it took 6x longer even though
it did 20x fewer loops.  Also it did 456 heap fetches (which were probably
nonsequential).  Vacuuming the table will probably help; if so, you should
consider setting parameter to encourage more frequent autovacuums:
| ALTER TABLE group_user SET (AUTOVACUUM_VACUUM_SCALE_FACTOR=0.005);

Justin