On 01/02/2017 05:23 AM, Job wrote:
Hello guys and very good new year to everybody!
We are now approaching some queries and statistics on very big table (about 180
millions of record).
The table is partitioned by day (about ~3 Gb of data for every partition/day).
We use Postgresql 9.6.1
I am experiencing quite important slowdown on queries.
I manually made a "vacuum full" and a "reindex" on every partition in order to
clean free space and reorder records.
I have a BRIN index on timestamp and index on other field (btree)
Starting by a simple query: explain analyze select count(domain) from
webtraffic_archive:
Other more complex queries are slower.
How can i improve it?
Records number can raise up until 1.000 millions.
Do i need a third-part tool for big data?
THANK YOU!
/F
I do very similar thing, log all my webstats to PG, but querying millions of
rows is always going to be slow. I use a summary table. Actually, several.
My detail table is like yours, but every 5 minutes I query out the last hour
and summarize into a by_hour table. Every night I query out the last 24 hours
and summarize into a by_day table. The detail table and by_hour table never
have more than 24 hours worth of data, by_day goes back many years.
My stats pages all query the by_hour and by_day tables, and its very fast.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
