analytics - improve performance on big logs

[fvanderbiest]

When the ogc-statistics database gets huge, analytics takes too much time to display the stats.

[fvanderbiest]

The culprit is this seq scan:

->  Seq Scan on ogc_services_log  (cost=0.00..1437477.65 rows=982 width=30) (actual time=478.463..12177.526 rows=1163185 loops=1)

...because we're not using the index with this where clause:
https://github.com/georchestra/georchestra/blob/14.06/analytics/src/main/java/org/georchestra/analytics/model/OGCStatsModel.java#L17

Better use

date >= '2014-08-01 0:0'::timestamp and date < '2014-09-01 0:0'::timestamp

which enables a far speedier index scan.

[fvanderbiest]

Monthly requests optimized with f67a9a1

For the global stats, we might need to consolidate the results in a separate table.

[pmauduit]

Or maybe a calculated table maintained regularly by the analytics apps ? (just some random thoughts)

[fvanderbiest]

@Vampouille is the "Hero of the Day", see http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

[Vampouille]

I saw several indexes on log table, maybe we can speed up insertion by removing those indexes (for example : layer, service) without slowing down "select" queries

[fvanderbiest]

+1, thanks

[Vampouille]

I just finished some tests with 6M records on my desktop. Query duration changes from 2500ms to 80ms with the following modifications :

• create a hierarchy of tables (inherited postgres tables) on date field (partitions)
• remove execution of prepared statements and replace with native query : executeQuery()
• I also remove all indexes

I think I will commit those changes in the beginning of next week.

[fvanderbiest]

Mostly done with ad0ba48 but not yet merged into master (related PR is #1141)