OFFSET против ROW_NUMBER()

Как известно, Postgresql OFFSET требует, чтобы он просматривал все строки до тех пор, пока точка не дошла до того места, где вы просили, что делает его бесполезным для разбивки на страницы с помощью огромных наборов результатов, все медленнее и медленнее, когда OFFSET поднимается вверх.

PG 8.4 теперь поддерживает функции окна. Вместо:

SELECT * FROM table ORDER BY somecol LIMIT 10 OFFSET 500

Вы можете сказать:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY somecol ASC) AS rownum FROM table) AS foo
WHERE rownum > 500 AND rownum <= 510

Помогают ли последние помочь нам вообще? Или нам нужно продолжать использовать идентификационные столбцы и временные таблицы для большой разбивки на страницы?

Ответ 1

Я построил тест, который сравнивает OFFSET, курсоры и ROW_NUMBER(). Мое впечатление от ROW_NUMBER(), что оно будет последовательным в скорости, независимо от того, где вы находитесь в результирующем наборе, является правильным. Тем не менее, эта скорость значительно медленнее, чем OFFSET или CURSOR, что, как и мое впечатление, практически одинаково по скорости, и ухудшает скорость до конца результата, который вы идете.

Результаты:

offset(100,100): 0.016359
scroll(100,100): 0.018393
rownum(100,100): 15.535614

offset(100,480000): 1.761800
scroll(100,480000): 1.781913
rownum(100,480000): 15.158601

offset(100,999900): 3.670898
scroll(100,999900): 3.664517
rownum(100,999900): 14.581068

В тесте script используется sqlalchemy для настройки таблиц и 1000000 строк тестовых данных. Затем он использует курсор psycopg2 для выполнения каждого оператора SELECT и получения результатов с помощью трех разных методов.

from sqlalchemy import *

metadata = MetaData()
engine = create_engine('postgresql://scott:[email protected]/test', echo=True)

t1 = Table('t1', metadata,
    Column('id', Integer, primary_key=True),
    Column('d1', String(50)),
    Column('d2', String(50)),
    Column('d3', String(50)),
    Column('d4', String(50)),
    Column('d5', String(50))
)

if not engine.has_table('t1'):
    conn = engine.connect()
    t1.create(conn)

    # 1000000 rows
    for i in range(100):
        conn.execute(t1.insert(), [
            dict(
                ('d%d' % col, "data data data %d %d" % (col, (i * 10000) + j))
                for col in range(1, 6)
            ) for j in xrange(1, 10001)
        ])

import time

def timeit(fn, count, *args):
    now = time.time()
    for i in xrange(count):
        fn(*args)
    total = time.time() - now
    print "%s(%s): %f" % (fn.__name__, ",".join(repr(x) for x in args), total)

# this is a raw psycopg2 connection.
conn = engine.raw_connection()

def offset(limit, offset):
    cursor = conn.cursor()
    cursor.execute("select * from t1 order by id limit %d offset %d" % (limit, offset))
    cursor.fetchall()
    cursor.close()

def rownum(limit, offset):
    cursor = conn.cursor()
    cursor.execute("select * from (select *, "
                    "row_number() over (order by id asc) as rownum from t1) as foo "
                    "where rownum>=%d and rownum<%d" % (offset, limit + offset))
    cursor.fetchall()
    cursor.close()

def scroll(limit, offset):
    cursor = conn.cursor('foo')
    cursor.execute("select * from t1 order by id")
    cursor.scroll(offset)
    cursor.fetchmany(limit)
    cursor.close()

print 

timeit(offset, 10, 100, 100)
timeit(scroll, 10, 100, 100)
timeit(rownum, 10, 100, 100)

print 

timeit(offset, 10, 100, 480000)
timeit(scroll, 10, 100, 480000)
timeit(rownum, 10, 100, 480000)

print 

timeit(offset, 10, 100, 999900)
timeit(scroll, 10, 100, 999900)
timeit(rownum, 10, 100, 999900)

Ответ 2

Используйте CURSOR для большого набора результатов, будет намного быстрее. Для небольших результирующих наборов конструкция LIMIT OFFSET работает нормально, но она имеет ограничения.

ROW_NUMBER - хорошая вещь, но не для разбивки на страницы. В результате вы получаете плохую производительность из-за последовательного сканирования.