Since I was doing it only for the testing purposes and on a development server which has only 8 GB of RAM, I used only 10m rows. But the original table has 1.5 billion rows. We will obviously be using a server with very high capacity, but I am not satisfied with the performance at all. This might be only a start, so I might get a better performance later.
Yes, the view is complex and almost is created by using 10 tables. Same goes with other views as well but this is what we are using in Netezza as well. And we are getting results of the full report in less than 5 seconds. And add to that, this is only a very little part of the whole query used in a report.
I will post the result of whole query with Explain analyze tomorrow.
We might even consider taking experts advice on how to tune queries and server, but if postgres is going to behave like this, I am not sure we would be able to continue with it.
Having said that, I would day again that I am completely new to this territory, so I might miss lots and lots of thing.
Hi,
On 13.3.2015 20:59, Vivekanand Joshi wrote:
> I am really worried about the performance of PostgreSQL as we have
> almost 1.5 billion records in promotion history table. Do you guys
In the previous message you claimed the post table has 10M rows ...
> really think PostgreSQL can handle this much load. We have fact
> tables which are more than 15 GB in size and we have to make joins
> with those tables in almost every query.
That depends on what performance you're looking for. You'll have to
provide considerably more information until we can help you. You might
want to check this:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
You have not provided the full query, just a query apparently
referencing views, so that the actual query is way more complicated.
Also, plain EXPLAIN is not really sufficient, we need EXPLAIN ANALYZE.
regards
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
