Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYblE-0004PU-JN for pgsql-hackers@arkaria.postgresql.org; Wed, 05 Jun 2019 19:39:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hYblD-0006A2-6p for pgsql-hackers@arkaria.postgresql.org; Wed, 05 Jun 2019 19:39:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYblC-00069v-QM for pgsql-hackers@lists.postgresql.org; Wed, 05 Jun 2019 19:39:46 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hYbl9-0006Ek-V3 for pgsql-hackers@postgresql.org; Wed, 05 Jun 2019 19:39:45 +0000 Received: from pps.filterd (smtp03.optiver.com [127.0.0.1]) by smtp03.optiver.com (8.16.0.27/8.16.0.27) with SMTP id x55JVK6o004617; Wed, 5 Jun 2019 21:39:30 +0200 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 2suey5a8nj-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Wed, 05 Jun 2019 21:39:30 +0200 Received: from opammb0561.comp.optiver.com (10.131.160.1) by opammb0561.comp.optiver.com (10.131.160.1) with Microsoft SMTP Server (TLS) id 15.0.1263.5; Wed, 5 Jun 2019 21:39:30 +0200 Received: from opammb0561.comp.optiver.com ([::1]) by opammb0561.comp.optiver.com ([fe80::d550:2c12:6cc:8457%17]) with mapi id 15.00.1263.000; Wed, 5 Jun 2019 21:39:30 +0200 From: Floris Van Nee To: Dmitry Dolgov <9erthalion6@gmail.com>, Jesper Pedersen CC: Rafia Sabih , Jeff Janes , Thomas Munro , "Alexander Kuzmenkov" , Peter Geoghegan , "Tomas Vondra" , Thomas Munro , Bhushan Uparkar , PostgreSQL-development , Alexander Korotkov , James Coleman Subject: Re: Index Skip Scan Thread-Topic: Index Skip Scan Thread-Index: AQHVG9Nlf96cjiiphUuQXFO9+5YF3KaNdPx0 Date: Wed, 5 Jun 2019 19:39:29 +0000 Message-ID: <1559763570329.85227@Optiver.com> References: <2685f020-f302-9bf6-1b5c-ebc2bcebc635@2ndquadrant.com> <20190315.125452.252656146.horiguchi.kyotaro@lab.ntt.co.jp> <1559361698401.22864@Optiver.com> <1559362223126.18661@Optiver.com> <988b7763-5c55-1be1-b7b6-f3a68eff0d40@redhat.com>,,<1559763453006.85615@Optiver.com> In-Reply-To: <1559763453006.85615@Optiver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.131.160.182] x-tm-as-product-ver: SMEX-11.0.0.1284-8.100.1062-24660.002 x-tm-as-result: No--5.274600-0.000000-31 x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_155976357032985227Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-06-05_11:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_155976357032985227Optivercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > To address this, probably we can do something like in the attached patch. > Altogether with distinct_pathkeys uniq_distinct_pathkeys are stored, whic= h is > the same, but without the constants elimination. It's being used then for > getting the real number of distinct keys, and to check the order of the c= olumns > to not consider index skip scan if it's different. Hope it doesn't > look too hacky. > Thanks! I've verified that it works now. I was wondering if we're not too strict in some cases now though. Consider = the following queries: postgres=3D# explain(analyze) select distinct on (m,f) m,f from t where m= =3D'M2'; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------------- Index Only Scan using t_m_f_t_idx on t (cost=3D0.29..11.60 rows=3D40 widt= h=3D5) (actual time=3D0.056..0.469 rows=3D10 loops=3D1) Scan mode: Skip scan Index Cond: (m =3D 'M2'::text) Heap Fetches: 10 Planning Time: 0.095 ms Execution Time: 0.490 ms (6 rows) postgres=3D# explain(analyze) select distinct on (f) m,f from t where m=3D'= M2'; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------------------- Unique (cost=3D0.29..849.83 rows=3D10 width=3D5) (actual time=3D0.088..10= .920 rows=3D10 loops=3D1) -> Index Only Scan using t_m_f_t_idx on t (cost=3D0.29..824.70 rows=3D= 10052 width=3D5) (actual time=3D0.087..8.524 rows=3D10000 loops=3D1) Index Cond: (m =3D 'M2'::text) Heap Fetches: 10000 Planning Time: 0.078 ms Execution Time: 10.944 ms (6 rows) This is basically the opposite case - when distinct_pathkeys matches the fi= ltered list of index keys, an index skip scan could be considered. Currentl= y, the user needs to write 'distinct m,f' explicitly, even though he specif= ies in the WHERE-clause that 'm' can only have one value anyway. Perhaps it= 's fine like this, but it could be a small improvement for consistency. -Floris? --_000_155976357032985227Optivercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

> To address this, probably we can do something like in the attache= d patch.
> Altogether with dist= inct_pathkeys uniq_distinct_pathkeys are stored, which is
> the same, but withou= t the constants elimination. It's being used then for
> getting the real num= ber of distinct keys, and to check the order of the columns
> to not consider inde= x skip scan if it's different. Hope it doesn't
> look too hacky.
> 

Thanks! I've verified that it wo= rks now.
I was wondering if we're not too stri= ct in some cases now though. Consider the following queries:

postgres=3D# explain(analyze) select distinct on (m,f) m,f from t wher= e m=3D'M2';
                    =                      = ;             QUERY PLAN      =                      = ;                     &nb= sp;      
----------------------------------------------------------------------= -------------------------------------------------
 Index Only Scan using t_m_f_t_idx on t  (cost=3D0.29..11.60= rows=3D40 width=3D5) (actual time=3D0.056..0.469 rows=3D10 loops=3D1)
   Scan mode: Skip scan
   Index Cond: (m =3D 'M2'::text)
   Heap Fetches: 10
 Planning Time: 0.095 ms
 Execution Time: 0.490 ms
(6 rows)

postgres=3D# explain(analyze) select distinct on (f) m,f from t where = m=3D'M2';
                    =                      = ;                    QUER= Y PLAN                   =                     &nbs= p;                    
----------------------------------------------------------------------= --------------------------------------------------------------
 Unique  (cost=3D0.29..849.83 rows=3D10 width=3D5) (actual t= ime=3D0.088..10.920 rows=3D10 loops=3D1)
   ->  Index Only Scan using t_m_f_t_idx on t  = (cost=3D0.29..824.70 rows=3D10052 width=3D5) (actual time=3D0.087..8.524 ro= ws=3D10000 loops=3D1)
         Index Cond: (m =3D 'M2'::text)
         Heap Fetches: 10000
 Planning Time: 0.078 ms
 Execution Time: 10.944 ms
(6 rows)

This is basically the opposite case - when distinct_pathkeys matches t= he filtered list of index keys, an index skip scan could be considered. Cur= rently, the user needs to write 'distinct m,f' explicitly, even though he s= pecifies in the WHERE-clause that 'm' can only have one value anyway. Perhaps it's fine like this, but it could = be a small improvement for consistency.

-Floris​

--_000_155976357032985227Optivercom_--