Как повысить эффективность запросов для большого объема данных в базе данных PostgreSQL?

У меня есть база данных PostgreSQL с 1,2 миллиарда строк, и я попытался создать приложение, которое запрашивает миллион строк за раз, с возможностью запрашивать большие интервалы. Сначала я просто запросил базу данных размером от миллиона до 10 миллионов;
Теперь, когда я запрашиваю большую базу данных с помощью OFFSET, создание ResultSet занимает много времени.

   // ...
   stmt.setFetchSize(100000);
   ResultSet rs = stmt.executeQuery("SELECT mmsi, report_timestamp, position_geom, ST_X(position_geom) AS Long, "
                        + "ST_Y(position_geom) AS Lat FROM reports4 WHERE position_geom IS NOT NULL ORDER by report_timestamp ASC LIMIT "
                        + limit + " OFFSET " + set);

Таким образом, ORDER BY, вероятно, убивает мое время выполнения, но упорядоченная информация упрощает дальнейшую работу. Есть ли более эффективный способ запрашивать строки с интервалами?


postgresql java jdbc postgresql-performance
person guy_sensei    schedule 20.07.2015    source источник
comment
Какие индексы есть у вас за столом?   -  person user3707125    schedule 20.07.2015
comment
Если бы ваша таблица была проиндексирована на report_timestamp, это было бы почти бесполезно. Я думаю, что есть также частичные индексы, которые могут точно соответствовать вашему предложению where.   -  person Marko Topolnik    schedule 20.07.2015
comment
wiki.postgresql.org/wiki/Slow_Query_Questions и stackoverflow.com/tags/postgresql-performance/info   -  person a_horse_with_no_name    schedule 20.07.2015

Ответы (2)


arrow_upward
3
arrow_downward

Для этого запроса:

SELECT mmsi, report_timestamp, position_geom, ST_X(position_geom) AS Long, "
                        + "ST_Y(position_geom) AS Lat
FROM reports4
WHERE position_geom IS NOT NULL
ORDER by report_timestamp ASC;

Вы должны иметь возможность использовать индекс для выражения:

CREATE INDEX idx_reports4_position_ts ON reports4((position_geom IS NOT NULL), report_timestamp)

Этот индекс следует использовать непосредственно для запроса.

person Gordon Linoff    schedule 20.07.2015
comment
Большое спасибо @Gordon Linoff, я не знал об INDEX, я почитаю :) - person guy_sensei; 20.07.2015
comment
Это разделяет индекс на нулевые и не нулевые случаи; более высокая производительность, вероятно, будет достигнута с частичным индексом. - person Marko Topolnik; 20.07.2015
comment
@guy_sensei: вау, 1,2 миллиарда строк, и тебе никогда не приходилось создавать индекс? У вас должно быть очень мощное оборудование... - person a_horse_with_no_name; 20.07.2015

arrow_upward
2
arrow_downward

Вы можете использовать частичный индекс, построенный по подмножеству вашей базы данных.

CREATE INDEX idx_reports4 ON reports4(position_geom, report_timestamp) where position_geom IS NOT NULL;

Это значительно повысит производительность, поскольку вы просто индексируете часть необходимой базы данных.

person Constantine    schedule 21.07.2015