Query execution time Vs Cost

Hi, As part of one query tuning, it was observed that query execution time was more even though cost was decreased. *Initial Query :* Nested Loop Left Join (cost=159.88..*38530.02* rows=1 width=8) (actual time=0.387..*40.766* rows=300 loops=1) *Changed Query :* Nested Loop Anti Join (cost=171

Re: Query execution time Vs Cost

Hi Justin, Thanks a lot for the detailed analysis and explanation for slowness that was seen. Pointed noted related to the vacuum tuning option. Regards, Amarendra On Sat, Sep 14, 2019 at 4:36 AM Justin Pryzby wrote: > On Fri, Sep 13, 2019 at 04:38:50PM +0530, Amarendra Konda wrote: >

Autovacuum is cleaning very less dead tuples

Hi, As part of vacuum tuning, We have set the below set of parameters. *> select relname,reloptions, pg_namespace.nspname from pg_class join pg_namespace on pg_namespace.oid=pg_class.relnamespace where relname IN('process_instance') and pg_namespace.nspname='public'; relname| reloptio

Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Hi, PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit We have noticed huge difference interms of execution plan ( response time) , When we pass the direct values Vs inner query to IN clause. High level details of the us

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

Index Cond: ((pa.process_instance_id = ANY ('{137074941043913,164357609323111}'::bigint[])) AND (pa.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone)) Buffers: shared hit=55 Planning time: 1.710 ms Executio

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

86 ms (20 rows) On Thu, May 7, 2020 at 9:17 PM David G. Johnston wrote: > On Thu, May 7, 2020 at 7:40 AM Adrian Klaver > wrote: > >> On 5/7/20 4:19 AM, Amarendra Konda wrote: >> > Hi, >> > >> > PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-l

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

ers: shared hit=43065 Planning time: 0.455 ms Execution time: 85.830 ms On Thu, May 7, 2020 at 11:19 PM Amarendra Konda wrote: > Hi David, > > Thanks for the reply.This has optimized number of rows. > > Can you please explain, why it is getting more columns in output, even

Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )

ey" PRIMARY KEY, btree (process_instance_id) "*fki_conv_konotor_user_user_id*" btree (user_id) WITH (fillfactor='70') Regards, Amarendra On Fri, May 8, 2020 at 12:01 AM Virendra Kumar wrote: > Sending table structure with indexes might help little further in >