Re: Hash join on int takes 8..114 seconds

От: Andrus
Тема: Re: Hash join on int takes 8..114 seconds
Дата: ,
Msg-id: ggcihb$1v26$2@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join on int takes 8..114 seconds  (Tomas Vondra)
Ответы: Re: Hash join on int takes 8..114 seconds  (Tomas Vondra)
Список: pgsql-performance

Скрыть дерево обсуждения

Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (PFC, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (PFC, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
     Re: Hash join on int takes 8..114 seconds  (, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
       Re: Hash join on int takes 8..114 seconds  (Alan Hodgson, )
       Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
     Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
     Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  (Tom Lane, )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
         Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (, )
              Re: Hash join on int takes 8..114 seconds  ("Andrus", )

> I guess you have backups - take them, restore the database on a different
> machine (preferably with the same / similar hw config) and tune the
> queries on it.
>
> After restoring all the tables / indexes will be 'clean' (not bloated), so
> you'll see if performing VACUUM FULL / CLUSTER is the right solution or if
> you have to change the application internals.
>
> Sure, the times will be slightly different but the performance problems
> should remain the same.

VACUUM FULL has
My test computer has PostgreSql 8.3, 4 GB RAM, SSD disks, Intel X2Extreme
CPU
So it is much faster than this prod server.
No idea how to emulate this environment.
I can create new db in prod server as old copy but this can be used in late
night only.

Where to find script which clones some database in server? Something like

CREATE DATABASE newdb AS SELECT *  FROM olddb;

It would be more convenient to run db cloning script from pgadmin command
window.
Only way I found is to use SSH with pg_dup/pg_restore. This requires SSH
access to server and SSH port opening to public  internet.

Or probably try to run CLUSTER command in prod server. Hopefully clustering
by product id cannot make things slow
too much.

Andrus.



В списке pgsql-performance по дате сообщения:

От: Tomas Vondra
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds
От: Richard Huxton
Дата:
Сообщение: Re: Increasing pattern index query speed