Postgres Professional
en

Re: Foreign key slows down copy/insert

От: PFC
Тема: Re: Foreign key slows down copy/insert
Дата: ,
Msg-id: op.so9q4exeth1vuj@localhost
(см: обсуждение, исходный текст)
Ответ на: Re: Foreign key slows down copy/insert  (PFC)
Ответы: Re: Foreign key slows down copy/insert  (Richard van den Berg)
Список: pgsql-performance

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

Foreign key slows down copy/insert  (Richard van den Berg, )
 Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
  Re: Foreign key slows down copy/insert  (Richard van den Berg, )
   Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
    Re: Foreign key slows down copy/insert  (Richard van den Berg, )
     Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
      Re: Foreign key slows down copy/insert  (Richard van den Berg, )
       Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
        Re: Foreign key slows down copy/insert  (Marko Ristola, )
      Re: Foreign key slows down copy/insert  (Tom Lane, )
   Re: Foreign key slows down copy/insert  (Stephan Szabo, )
    Re: Foreign key slows down copy/insert  (Tom Lane, )
     Re: Foreign key slows down copy/insert  (Stephan Szabo, )
 Re: Foreign key slows down copy/insert  (PFC, )
  Re: Foreign key slows down copy/insert  (Richard van den Berg, )
  Re: Foreign key slows down copy/insert  (PFC, )
   Re: Foreign key slows down copy/insert  (Richard van den Berg, )
    Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
     Re: Foreign key slows down copy/insert  (Richard van den Berg, )
      Re: Foreign key slows down copy/insert  (Tom Lane, )
       Re: Foreign key slows down copy/insert  (Richard van den Berg, ) 
> Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual
> time=93.824..93.826 rows=1 loops=1)
>     Index Cond: (id = $0)
>     InitPlan
>       ->  Limit  (cost=0.00..0.04 rows=1 width=4) (actual
> time=15.128..15.129 rows=1 loops=1)
>             ->  Seq Scan on A  (cost=0.00..47569.70 rows=1135570
> width=4) (actual time=15.121..15.121 rows=1 loops=1)
>   Total runtime: 94.109 ms

    94 ms for an index scan ?
    this look really slow...

    was the index in the RAM cache ? does it fit ? is it faster the second
time ? If it's still that slow, something somewhere is severely screwed.

    B has 150K rows you say, so everything about B should fit in RAM, and you
should get 0.2 ms for an index scan, not 90 ms !
    Try this :

    Locate the files on disk which are involved in table B (table + indexes)
looking at the system catalogs
    Look at the size of the files. Is the index severely bloated ? REINDEX ?
DROP/Recreate the index ?
    Load them into the ram cache (just cat files | wc -b several times until
it's almost instantaneous)
    Retry your query and your COPY

    I know it's stupid... but it's a lot faster to load an index in the cache
by plainly reading the file rather than accessing it randomly.
    (even though, with this number of rows, it should not be THAT slow !)

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

Предыдущее:
От: "Mohan, Ross"
Дата:
Сообщение: Spend 7K *WHERE*? WAS Intel SRCS16 SATA raid? and How to Improve w/7K$?
Следующее:
От: Greg Stark
Дата:
Сообщение: Re: How to improve db performance with $7K?