Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1duz7T-0002zw-Sm for pgsql-performance@arkaria.postgresql.org; Thu, 21 Sep 2017 10:54:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1duz7S-0002il-Lu for pgsql-performance@arkaria.postgresql.org; Thu, 21 Sep 2017 10:54:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1duz5i-00082c-Pd for pgsql-performance@postgresql.org; Thu, 21 Sep 2017 10:52:22 +0000 Received: from mail-lf0-x22f.google.com ([2a00:1450:4010:c07::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1duz5b-0000KB-QC for pgsql-performance@postgresql.org; Thu, 21 Sep 2017 10:52:21 +0000 Received: by mail-lf0-x22f.google.com with SMTP id u21so5316355lfk.12 for ; Thu, 21 Sep 2017 03:52:15 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=5DfUuroOQVWk4Gm3+3dFu9chMNLbjY5FO/RG0bbwsKA=; b=r6aUGvgsvyht5kbSkrYmj/G7UATwRHyCzJHdk/7D8cApm5j3chqlXnbRTqmU4JzxkY x96DOHjdYYkCjB0mGYcEQHc2wSaydzfn1hUvsMzaafwna5oR0dUb3phVpCvhdTtI8AGE dxCRd60jHlzOBrLRSKVw9kNEFO+rHDDrUXM5wNHIrL6Fc1f4u7krhed/kL3qhcVCJf2L zKhC3JscU8b1WdoWlxBZrsqG8uRxxD0n/bKEaSBlT8v4WwVLmGYHAWwxkxuovGWN/FKR cB+lBoUThYyWJKFcqF9yZABYyf33YU+Qgzb2wzbxaTxdda7yKV3YjGlqQ+nOrwyauEQF fBug== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=5DfUuroOQVWk4Gm3+3dFu9chMNLbjY5FO/RG0bbwsKA=; b=q5ukRLj7pl4XQv9Iu36JBRFlYjOC+owddWnR5byoEbEx9m3fJKsAf11dj1RH0gfT6m lEQdn2/qx9auEzB4t5gj+p5W/5O61OuphupdUKcRH94lO9xvhhqCdaGMmYoh/HfMCfvl bxytG8rYjw6q9wDPk9x1oI22fzBq2bNIwOniezu8Xo0HXrsGvON/lOIgiy7GMVUVBZpf kyjCxjxHMe7lWbbdyDiTag5og67/XeFRNcd3qAOUefyD9tziW25VHy/Art0PnFBcfUYt F2YPG3IdiwzuNVqxZX/ULMhEW6cC5SBftPqSrUxVMkppw22PTw3PChS7+KoBO1wyC82f 7yLg== X-Gm-Message-State: AHPjjUhu1yCZ3eg5FsS6tEjyijhxEUV8vpTBMERbBJJ7N9gOM05qxlkc 1cs7VX3Tlor+zGsL1H4Jr3u0dVPsBUJBR242mnLgEQ== X-Google-Smtp-Source: AOwi7QBFtsqR7h9+NMKnH1n5ebIsEnob1Uo0+FbvsBWE7UZqg+moIGqGMijotxBVwfW/h5aWnM/2u8OfPDw/xvSZBvg= X-Received: by 10.25.168.135 with SMTP id r129mr687957lfe.233.1505991132317; Thu, 21 Sep 2017 03:52:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.235.85 with HTTP; Thu, 21 Sep 2017 03:52:11 -0700 (PDT) From: Subramaniam C Date: Thu, 21 Sep 2017 16:22:11 +0530 Message-ID: Subject: Query regarding EXPLAIN (ANALYZE,BUFFERS) To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a114020c27a0fb30559b0e2ee" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a114020c27a0fb30559b0e2ee Content-Type: text/plain; charset="UTF-8" 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? Thanks and Regards Subramaniam --001a114020c27a0fb30559b0e2ee Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

I wanted to query top 20 rows by joining two tab= les, 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 =C2=A0939 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 usin= g the index and took only 5 secs.

Can you please explain how the ind= ex size got reduced after recreating it and how the query started using the= index after recreating?

Thanks and Regards
Subramaniam
--001a114020c27a0fb30559b0e2ee--