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

От: Tomas Vondra
Тема: Re: Hash join on int takes 8..114 seconds
Дата: ,
Msg-id: 4929E32C.8080400@fuzzy.cz
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join on int takes 8..114 seconds  ("Andrus")
Список: 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", )

> 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.

Well, a faster but comparable system may not be a problem - the query
might run 10 times faster, but it still will be slow (say 40 seconds
instead of 8 minutes).

What is a problem is a different I/O system - SSD instead of traditional
drives in this case. I have no direct experience with with SSD yet, but
AFAIK the seek time is much better compared to regular drives (say 0.1ms
instead of 10ms, that is 100-times faster).

So you can't just put on old SATA drive into the test machine?

> 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.

Yes, using pg_dump | pg_restore is probably the way to clone database.
But it will slow down the system, as it has to do a lot of I/O (and as
it seems to be a bottleneck already, I don't think this is a good idea).

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

As already noted, CLUSTER command causes exclusive lock on the database.
So this is an operation you'd like to do on production server ...

Tomas


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

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