On Wed, Jul 6, 2011 at 9:04 PM, Tomas Vondra <tv@xxxxxxxx> wrote: > Dne 6.7.2011 15:30, bakkiya napsal(a): >> Any help, please? > > According to the EXPLAIN ANALYZE output (please, don't post it to the > mailing list directly - use something like explain.depesz.com, I've done > that for you this time: http://explain.depesz.com/s/HMN), you're doing a > UNIQUE over a lot of data (2 million rows, 1.5GB). > > That is done by sorting the data, and sorting is very CPU intensive task > usually. So the fact that the CPU is 100% utilized is kind of expected > in this case. So that's a feature, not a bug. > > In general each process is hitting some bottleneck. It might be an I/O, > it might be a CPU, it might be something less visible (memory bandwidth > or something like that). > > But I've noticed one thing in your query - you're doing a UNIQUE in the > view (probably, we don't know the definition) and then once again in the > query (but using just one column from the view). > > The problem is the inner sort does not remove any rows (1979735 rows > in/out). Why do you do the UNIQUE in the view? Do you really need it > there? I guess removing it might significantly improve the plan. > > Try to do the query without the view - it seems it's just an union of > current tables and a history (both partitioned, so do something like this) > > SELECT DISTINCT init_service_comp FROM ( > SELECT init_service_comp FROM events > UNION > SELECT init_service_comp FROM hist_events > ) > > or maybe even > > SELECT DISTINCT init_service_comp FROM ( > SELECT DISTINCT init_service_comp FROM events > UNION > SELECT DISTINCT init_service_comp FROM hist_events > ) > > Let's see how that works - post EXPLAIN ANALYZE using explain.depesz.com In this case UNION ALL is probably more appropriate than UNION - and may have different performance characteristics (saving the UNIQUE?). Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
