From owner-freebsd-ports-bugs@FreeBSD.ORG Thu May 22 12:00:00 2014 Return-Path: Delivered-To: freebsd-ports-bugs@smarthost.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id CE52F794 for ; Thu, 22 May 2014 12:00:00 +0000 (UTC) Received: from freefall.freebsd.org (freefall.freebsd.org [IPv6:2001:1900:2254:206c::16:87]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id AB12029EE for ; Thu, 22 May 2014 12:00:00 +0000 (UTC) Received: from freefall.freebsd.org (localhost [127.0.0.1]) by freefall.freebsd.org (8.14.8/8.14.8) with ESMTP id s4MC00uf057567 for ; Thu, 22 May 2014 12:00:00 GMT (envelope-from gnats@freefall.freebsd.org) Received: (from gnats@localhost) by freefall.freebsd.org (8.14.8/8.14.8/Submit) id s4MC00tk057566; Thu, 22 May 2014 12:00:00 GMT (envelope-from gnats) Resent-Date: Thu, 22 May 2014 12:00:00 GMT Resent-Message-Id: <201405221200.s4MC00tk057566@freefall.freebsd.org> Resent-From: FreeBSD-gnats-submit@FreeBSD.org (GNATS Filer) Resent-To: freebsd-ports-bugs@FreeBSD.org Resent-Reply-To: FreeBSD-gnats-submit@FreeBSD.org, Michelle Sullivan Received: from mx1.freebsd.org (mx1.freebsd.org [8.8.178.115]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by hub.freebsd.org (Postfix) with ESMTPS id BB0F459E for ; Thu, 22 May 2014 11:51:24 +0000 (UTC) Received: from cgiserv.freebsd.org (cgiserv.freebsd.org [IPv6:2001:1900:2254:206a::50:4]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 8DC792994 for ; Thu, 22 May 2014 11:51:24 +0000 (UTC) Received: from cgiserv.freebsd.org ([127.0.1.6]) by cgiserv.freebsd.org (8.14.8/8.14.8) with ESMTP id s4MBpN5F075114 for ; Thu, 22 May 2014 11:51:23 GMT (envelope-from nobody@cgiserv.freebsd.org) Received: (from nobody@localhost) by cgiserv.freebsd.org (8.14.8/8.14.8/Submit) id s4MBpNtJ075111; Thu, 22 May 2014 11:51:23 GMT (envelope-from nobody) Message-Id: <201405221151.s4MBpNtJ075111@cgiserv.freebsd.org> Date: Thu, 22 May 2014 11:51:23 GMT From: Michelle Sullivan To: freebsd-gnats-submit@FreeBSD.org X-Send-Pr-Version: www-3.1 Subject: ports/190104: Patch to make ip4r prefer indexes on large (ie in production) databases X-BeenThere: freebsd-ports-bugs@freebsd.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: Ports bug reports List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 22 May 2014 12:00:01 -0000 >Number: 190104 >Category: ports >Synopsis: Patch to make ip4r prefer indexes on large (ie in production) databases >Confidential: no >Severity: non-critical >Priority: low >Responsible: freebsd-ports-bugs >State: open >Quarter: >Keywords: >Date-Required: >Class: update >Submitter-Id: current-users >Arrival-Date: Thu May 22 12:00:00 UTC 2014 >Closed-Date: >Last-Modified: >Originator: Michelle Sullivan >Release: 7.x, 8.x and 9.x >Organization: SORBS >Environment: N/A >Description: Patch to apply an optional patch on databases/ip4r to make the planner prefer indexes on ip4r joins when the tables are more like production size ;-) ... The ip4r plugin has no statistics collection for ip4r so it will use/prefer sequence scanning when tables exceed around 40,000 rows, or if the join is more complex, when using the extended operators (specifically those with GiST indexes) If the table doesn't use the GiST index this patch does nothing. If the table has only a few hundred rows forcing/preferring the GiST index can slow the queries The actual patch is a simple patch that just changes the internal weighting of the statistics calculation and was made originally for ip4r-v1.x with the help of the authors of the ip4r plugin. It has been modified to work on ip4r-2.x only now. >How-To-Repeat: N/A >Fix: Diff to the databases/ip4r directory that provides an option to apply is attached, and is available here: http://flashback.sorbs.net/packages/databases_ip4r-portoption-preferindex.patch Patch attached with submission follows: diff -ur ip4r.orig/Makefile ip4r/Makefile --- ip4r.orig/Makefile 2014-05-21 13:17:13.000000000 -0500 +++ ip4r/Makefile 2014-05-22 06:33:21.000000000 -0500 @@ -9,10 +9,22 @@ MAINTAINER= tobez@FreeBSD.org COMMENT= IP address and IP range index types for PostgreSQL +OPTIONS_DEFINE= IDXPATCH + +IDXPATCH_DESC= Prefer ip4r indexes over seq_scan on large datasets + USES= gmake pgsql MAKE_ARGS= USE_PGXS=1 +.include + +.if ${PORT_OPTIONS:MIDXPATCH} +# Patch if the option is supplied, however we need to unpatch if the option is de-selected after previously selecting (TODO) +PATCH_SITES= http://www.sorbs.net/home/:SORBS +PATCHFILES= ip4r-2.x-SORBS-prefer-index.patch:-p1:SORBS +.endif + # In order to make this port PREFIX-safe, we override the install target # and install the three files manually do-install: diff -ur ip4r.orig/distinfo ip4r/distinfo --- ip4r.orig/distinfo 2014-05-21 13:17:13.000000000 -0500 +++ ip4r/distinfo 2014-05-22 05:59:27.000000000 -0500 @@ -1,2 +1,4 @@ SHA256 (ip4r-2.0.tar.gz) = 251e70d12fc1244aab1707d3db0fe9f821ab224765e34cd8f2fb66ee3468a957 SIZE (ip4r-2.0.tar.gz) = 93007 +SHA256 (ip4r-2.x-SORBS-prefer-index.patch) = 4c10dcb0c31b6abe012597fc7826ccff2611c8ef256bbc5d448633cfb3b60427 +SIZE (ip4r-2.x-SORBS-prefer-index.patch) = 9221 >Release-Note: >Audit-Trail: >Unformatted: