On Sun, Feb 15, 2015 at 2:08 PM, Alexander Korotkov <aekorot...@gmail.com> wrote:
> Following changes has been made in attached patch:
>
> * Get sort operators from pathkeys.
> * Recheck argument of distance function has been reverted.
>
Few comments were added and pairing heap comparison function was fixed in
attached version of patch (knn-gist-recheck-6.patch).
Also I expected that reordering in executor would be slower than reordering
in GiST because of maintaining two heaps instead of one. I've revised
version of patch with reordering in GiST to use pairing heap. I compare two
types of reordering on 10^7 random points and polygons. Results are below.
Test shows that overhead of reordering in executor is insignificant (less
than statistical error).
Reorder in GiST Reorder in executor
points
limit=10 0.10615 0.0880125
limit=100 0.23666875 0.2292375
limit=1000 1.51486875 1.5208375
polygons
limit=10 0.11650625 0.1347
limit=100 0.46279375 0.45294375
limit=1000 3.5170125 3.54868125
Revised patch with reordering in GiST is attached
(knn-gist-recheck-in-gist.patch) as well as testing script (test.py).
------
With best regards,
Alexander Korotkov.
knn-gist-recheck-6.patch
Description: Binary data
knn-gist-recheck-in-gist.patch
Description: Binary data
#!/usr/bin/env python
import psycopg2
import json
import sys
# Create test tables with following statements.
#
# create table p as (select point(random(), random()) from generate_series(1,10000000));
# create index p_idx on p using gist(v);
# create table g as (select polygon(3 + (random()*5)::int, circle(point(random(), random()), 0.001)) v from generate_series(1,10000000));
# create index g_idx on g using gist(v);
dbconn = psycopg2.connect("dbname='postgres' user='smagen' host='/tmp' password='' port=5431")
points = []
pointsCount = 16
tableName = None
def generatePoints(n):
global points
m = 1
d = 0.5
points.append((0, 0))
while m <= n:
for i in range(0, m):
points.append((points[i][0] + d, points[i][1] ))
points.append((points[i][0] , points[i][1] + d))
points.append((points[i][0] + d, points[i][1] + d))
d /= 2.0
m *= 4
generatePoints(pointsCount)
def runKnn(point, limit):
cursor = dbconn.cursor()
cursor.execute("EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM " + tableName + " ORDER BY v <-> %s::point LIMIT %s;", (point, limit))
plan = cursor.fetchone()[0][0]
cursor.close()
return (plan['Planning Time'], plan['Execution Time'])
def makeTests(n, limit):
planningTime = 0
executionTime = 0
for i in range(0, n):
for j in range(0, pointsCount):
point = '(' + str(points[j][0]) + ',' + str(points[j][1]) + ')'
result = runKnn(point, limit)
planningTime += result[0]
executionTime += result[1]
planningTime /= n * pointsCount
executionTime /= n * pointsCount
return (planningTime, executionTime)
if (len(sys.argv) < 2):
print "Usage: %s table_name" % sys.argv[0]
sys.exit(2)
tableName = sys.argv[1]
for limit in [10, 100, 1000]:
result = makeTests(10, limit)
print "limit: %s\nplanning: %s\nexecution: %s" % (limit, result[0], result[1])
dbconn.close()
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
