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 1hX61c-0006kZ-C8 for pgsql-hackers@arkaria.postgresql.org; Sat, 01 Jun 2019 15:34:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hX61Z-00063Z-Rx for pgsql-hackers@arkaria.postgresql.org; Sat, 01 Jun 2019 15:34:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hX61Z-00063S-JE for pgsql-hackers@lists.postgresql.org; Sat, 01 Jun 2019 15:34:25 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hX61S-0007Ju-NR for pgsql-hackers@postgresql.org; Sat, 01 Jun 2019 15:34:25 +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 x51FV7AI009217; Sat, 1 Jun 2019 17:33:59 +0200 Received: from opammb0562.comp.optiver.com (opammb0562.comp.optiver.com [10.131.160.2]) by smtp03.optiver.com with ESMTP id 2suey5851w-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Sat, 01 Jun 2019 17:33:59 +0200 Received: from opammb0561.comp.optiver.com (10.131.160.1) by opammb0562.comp.optiver.com (10.131.160.2) with Microsoft SMTP Server (TLS) id 15.0.1263.5; Sat, 1 Jun 2019 17:33:58 +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; Sat, 1 Jun 2019 17:33:58 +0200 From: Floris Van Nee To: Dmitry Dolgov <9erthalion6@gmail.com> CC: Kyotaro HORIGUCHI , Jeff Janes , Thomas Munro , "Jesper Pedersen" , 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: AQHVGI0MquTJv1/pkkeRZn6/pKbsOg== Date: Sat, 1 Jun 2019 15:33:57 +0000 Message-ID: <1559403239721.74966@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>, In-Reply-To: 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-24652.000 x-tm-as-result: No--7.221600-0.000000-31 x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No Content-Type: multipart/alternative; boundary="_000_155940323972174966Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-06-01_10:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_155940323972174966Optivercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Thanks for the helpful replies. > Yes, good catch, I'll investigate. Looks like in the current implementati= on > something is not quite right, when we have this order of columns in an in= dex > and where clause (e.g. in the examples above everything seems fine if we = create > index over (feedcode, market) and not the other way around). I did a little bit of investigation and it seems to occur because in pathke= ys.c the function pathkey_is_redundant considers pathkeys redundant if ther= e is an equality condition with a constant in the corresponding WHERE claus= e. * 1. If the new pathkey's equivalence class contains a constant, and isn't * below an outer join, then we can disregard it as a sort key. An example= : * SELECT ... WHERE x =3D 42 ORDER BY x, y; In planner.c it builds the list of distinct_pathkeys, which is then used fo= r the index skip scan to skip over the first length(distinct_pathkeys) colu= mns when it does a skip. In my query, the distinct_pathkeys list only conta= ins 'feedcode' and not 'market', because 'market' was considered redundant = due to the WHERE clause. However, the index skip scan interprets this as th= at it has to skip over just the first column. We need to get this list of number of prefix columns to skip differently wh= ile building the plan. We need the 'real' number of distinct keys without t= hrowing away the redundant ones. However, I'm not sure if this information = can still be obtained while calling create_skipscan_unique_path? But I'm su= re people here will have much better ideas than me about this :-) -Floris --_000_155940323972174966Optivercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,


Thanks for the helpful replies.


> Yes, go= od catch, I'll investigate. Looks like in the current implementation
> something is not quite right, when we have this order of columns = in an index
> and where clause (e.g. in the examples above everything seems fin= e if we create
> index over (feedcode, market) and not the other way around).

I did a little bi= t of investigation and it seems to occur because in pathkeys.c the function=  pathkey_is_redundant considers pathkeys redundant if there is an equa= lity condition with a constant in the corresponding WHERE clause.

 * 1. If the new pathkey's equiv= alence class contains a constant, and isn't
 * below an outer join, then we = can disregard it as a sort key.  An example:
 * SELECT ... WHERE x =3D 42 ORDER BY x, y;

In planner.c it b= uilds the list of distinct_pathkeys, which is then used for the index = skip scan to skip over the first length(distinct_pathkeys) columns when it = does a skip. In my query, the distinct_pathkeys list only contains 'feedcode' and not 'market', because 'market' was consi= dered redundant due to the WHERE clause. However, the index skip scan inter= prets this as that it has to skip over just the first column.
We need to get th= is list of number of prefix columns to skip differently while building the = plan. We need the 'real' number of distinct keys without throwing away the = redundant ones. However, I'm not sure if this information can still be obtained while calling create_skipsc= an_unique_path? But I'm sure people here will have much better ideas than m= e about this :-)

-Floris
--_000_155940323972174966Optivercom_--