MIME-Version: 1.0
In-Reply-To: 
 <CAL=06W=MhSudwH5uS2b3oMBCnJzfP1OO-OrKdy8wTmX0HEDQTQ@mail.gmail.com>
References: 
 <CAL=06W=MhSudwH5uS2b3oMBCnJzfP1OO-OrKdy8wTmX0HEDQTQ@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 21 Sep 2017 20:37:34 +0200
Message-ID: 
 <CAFj8pRBJtUhQJ7XFDDULXDTbiw4G+3Z1J27LW7PmVb3GnN+DJQ@mail.gmail.com>
Subject: Re: Query regarding EXPLAIN (ANALYZE,BUFFERS)
To: Subramaniam C <subramaniam31784@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="f403043891082f3aa10559b76597"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--f403043891082f3aa10559b76597
Content-Type: text/plain; charset="UTF-8"

2017-09-21 12:52 GMT+02:00 Subramaniam C <subramaniam31784@gmail.com>:

> Hi
>
> I wanted to query top 20 rows by joining two tables, one table having
> around 1 lac rows and other table having 5 lac rows. Since I am using ORDER
> BY in the query so I created compound index with the columns being used in
> ORDER BY. Initially index size was  939 MB.
>
> Then I ran EXPLAIN(ANALYZE,BUFFERS) for this query which took around 20
> secs as it was not using the compound index for this query. So I drop this
> index and created again. The index size now got reduced to 559 MB.
>
> After this if I ran the EXPLAIN(ANALYZE,BUFFERS) for this query it was
> using the index and took only 5 secs.
>
> Can you please explain how the index size got reduced after recreating it
> and how the query started using the index after recreating?
>
>
The index can be bloated - when you recreate it or when you use REINDEX
command, then you remove a bloat content. VACUUM FULL recreate indexes too.

Fresh index needs less space on disc (the read is faster), in memory too
and has better structure - a access should be faster.


> Thanks and Regards
> Subramaniam
>

--f403043891082f3aa10559b76597
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><br><div class=3D"gmail_extra"><br><div class=3D"gmail_quo=
te">2017-09-21 12:52 GMT+02:00 Subramaniam C <span dir=3D"ltr">&lt;<a href=
=3D"mailto:subramaniam31784@gmail.com" target=3D"_blank">subramaniam31784@g=
mail.com</a>&gt;</span>:<br><blockquote class=3D"gmail_quote" style=3D"marg=
in:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"=
>Hi<br><br>I wanted to query top 20 rows by joining two tables, one table h=
aving around 1 lac rows and other table having 5 lac rows. Since I am using=
 ORDER BY in the query so I created compound index with the columns being u=
sed in ORDER BY. Initially index size was =C2=A0939 MB.<br><br>Then I ran E=
XPLAIN(ANALYZE,BUFFERS) for this query which took around 20 secs as it was =
not using the compound index for this query. So I drop this index and creat=
ed again. The index size now got reduced to 559 MB.<br><br>After this if I =
ran the EXPLAIN(ANALYZE,BUFFERS) for this query it was using the index and =
took only 5 secs.<br><br>Can you please explain how the index size got redu=
ced after recreating it and how the query started using the index after rec=
reating?<br><div><br></div></div></blockquote><div><br></div><div>The index=
 can be bloated - when you recreate it or when you use REINDEX command, the=
n you remove a bloat content. VACUUM FULL recreate indexes too.<br></div><d=
iv><br></div><div>Fresh index needs less space on disc (the read is faster)=
, in memory too and has better structure - a access should be faster. <br><=
/div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div di=
r=3D"ltr"><div></div><div>Thanks and Regards</div><span class=3D"HOEnZb"><f=
ont color=3D"#888888"><div>Subramaniam</div></font></span></div>
</blockquote></div><br></div></div>

--f403043891082f3aa10559b76597--