Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YQxgp-0002hU-NO for pgsql-performance@arkaria.postgresql.org; Thu, 26 Feb 2015 12:37:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YQxgp-0006DY-5a for pgsql-performance@arkaria.postgresql.org; Thu, 26 Feb 2015 12:37:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YQxgn-0006DR-UK for pgsql-performance@postgresql.org; Thu, 26 Feb 2015 12:37:14 +0000 Received: from mail-wg0-x22c.google.com ([2a00:1450:400c:c00::22c]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YQxgi-0001ew-VD for pgsql-performance@postgresql.org; Thu, 26 Feb 2015 12:37:12 +0000 Received: by wghn12 with SMTP id n12so10326574wgh.1 for ; Thu, 26 Feb 2015 04:37:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=sender:message-id:date:from:reply-to:user-agent:mime-version:to:cc :subject:references:in-reply-to:content-type :content-transfer-encoding; bh=ZgixebSOlLtv4LiXLyyq+y7RGY8FmXBb7qT/60/QELM=; b=tRhcOwwR33U+Gr9cXseWhi5NxPDr4Q59OMvH2LDcC5tw7ELfRdaQ+vkplJYOfX6dI5 hsemNAgP8y7u1V8ho4fZvIFGYQBm68qiTqF/Ui30qG6Hwf2yQdNsbk3T1PqXUU/eug5Q rTUoAIyLKxUBQ8BrkOsGGoI3knRt4x78JBwWKf6Z6DmXPxaDY1puF3yBzjl/uJ8CF/vJ FTUN4USfMKxzglThCj8vqDkTqfepBt3SUXRTWqP8ZPdQlRERv9/sAmf5ve2zNaFihoZ7 YFiXtQfJD8QjeDuW8JPiNzcWbsu2dDQ3P5CXqBU2HNpRVpIgf+em73wfy8OIX7Z/6nEQ CO3g== X-Received: by 10.194.61.51 with SMTP id m19mr16412277wjr.39.1424954228066; Thu, 26 Feb 2015 04:37:08 -0800 (PST) Received: from [192.168.1.109] (dsl-hkibrasgw1-58c38f-82.dhcp.inet.fi. [88.195.143.82]) by mx.google.com with ESMTPSA id m9sm29656093wiz.24.2015.02.26.04.37.06 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 26 Feb 2015 04:37:06 -0800 (PST) Message-ID: <54EF1370.2000006@iki.fi> Date: Thu, 26 Feb 2015 14:37:04 +0200 From: Heikki Linnakangas Reply-To: hlinnaka@iki.fi User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Icedove/31.4.0 MIME-Version: 1.0 To: Josh Berkus CC: "Sven R. Kunze" , Tom Lane , pgsql-performance@postgresql.org Subject: Re: Reverse Key Index References: <54DF9036.8080502@tbz-pariv.de> <7978.1423937899@sss.pgh.pa.us> <54DF9559.8080109@tbz-pariv.de> <54EE4D45.1060704@agliodbs.com> In-Reply-To: <54EE4D45.1060704@agliodbs.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.3 (--) 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 On 02/26/2015 12:31 AM, Josh Berkus wrote: > On 02/14/2015 10:35 AM, Sven R. Kunze wrote: >> Thanks for the immediate reply. >> >> I understand the use case is quite limited. >> >> On the other hand, I see potential when it comes to applications which >> use PostgreSQL. There, programmers would have to change a lot of code to >> tweak existing (and more importantly working) queries to hash/reverse an >> id column first. Using ORMs would make this change even more painful and >> maybe even impossible. >> >> When reading >> https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/ >> carefully, it also seems to work with index scan partially in case of >> equality comparisons. > > Seems like a good use for SP-GiST. Go for it! A b-tree opclass that just compares from right-to-left would work just as well, and perform better. - Heikki -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance