Search Postgresql Archives

Re: Most efficient way to insert without duplicates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I would also give it a try on turning on statistics on service_id and follower_id fields and tune collecting of distinct values for the optimizer.

Cheers,

Amador A.


On Wed, Apr 24, 2013 at 9:04 AM, François Beausoleil <francois@xxxxxxxxxxx> wrote:
Hi!

Le 2013-04-17 à 14:15, Jeff Janes a écrit :

On Wed, Apr 17, 2013 at 4:26 AM, François Beausoleil <francois@xxxxxxxxxxx> wrote:


 Insert on public.persona_followers  (cost=139261.12..20483497.65 rows=6256498 width=16) (actual time=4729255.535..4729255.535 rows=0 loops=1)
   Buffers: shared hit=33135295 read=4776921
   ->  Subquery Scan on t1  (cost=139261.12..20483497.65 rows=6256498 width=16) (actual time=562265.156..578844.999 rows=6819520 loops=1)


It looks like 12% of the time is being spent figuring out what rows to insert, and 88% actually doing the insertions.

So I think that index maintenance is killing you.  You could try adding a sort to your select so that rows are inserted in index order, or inserting in batches in which the batches are partitioned by service_id (which is almost the same thing as sorting, since service_id is the lead column)


To close out the thread, the final results are in http://explain.depesz.com/s/xOe :

Insert on public.persona_followers  (cost=149905.33..149906.58 rows=100 width=24) (actual time=19.837..19.837 rows=0 loops=1)
  Buffers: shared hit=206, local hit=1 read=105
  ->  Sort  (cost=149905.33..149905.58 rows=100 width=24) (actual time=19.534..19.536 rows=6 loops=1)
        Output: persona_followers_import.service_id, persona_followers_import.follower_id, (min(persona_followers_import.valid_at))
        Sort Key: persona_followers_import.follower_id
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=176, local hit=1 read=105
        ->  HashAggregate  (cost=149901.01..149902.01 rows=100 width=24) (actual time=19.514..19.526 rows=6 loops=1)
              Output: persona_followers_import.service_id, persona_followers_import.follower_id, min(persona_followers_import.valid_at)
              Buffers: shared hit=176, local hit=1 read=105
              ->  Bitmap Heap Scan on pg_temp_35.persona_followers_import  (cost=93051.86..149734.25 rows=22234 width=24) (actual time=14.350..19.505 rows=6 loops=1)
                    Output: persona_followers_import.service_id, persona_followers_import.follower_id, persona_followers_import.valid_at
                    Recheck Cond: ((persona_followers_import.service_id = 362513855) AND (persona_followers_import.follower_id IS NOT NULL))
                    Filter: (NOT (hashed SubPlan 1))
                    Buffers: shared hit=176, local hit=1 read=105
                    ->  Bitmap Index Scan on persona_followers_import_service_id  (cost=0.00..1134.32 rows=44469 width=0) (actual time=1.752..1.752 rows=10000 loops=1)
                          Index Cond: ((persona_followers_import.service_id = 362513855) AND (persona_followers_import.follower_id IS NOT NULL))
                          Buffers: local hit=1 read=40
                    SubPlan 1
                      ->  Bitmap Heap Scan on public.persona_followers  (cost=661.54..91851.35 rows=24252 width=8) (actual time=2.309..6.400 rows=14317 loops=1)
                            Output: public.persona_followers.follower_id
                            Recheck Cond: (public.persona_followers.service_id = 362513855)
                            Buffers: shared hit=176
                            ->  Bitmap Index Scan on persona_followers_pkey  (cost=0.00..655.48 rows=24252 width=0) (actual time=2.284..2.284 rows=14317 loops=1)
                                  Index Cond: (public.persona_followers.service_id = 362513855)
                                  Buffers: shared hit=88
Total runtime: 19.917 ms

Runtime is under 20 milliseconds, per imported service_id. I have a few thousand such items per day, and that's fine. The final script looks like this:

CREATE TEMPORARY TABLE persona_followers_import( service_id bigint, follower_id bigint );
COPY TO persona_followers_import FROM stdin;
...
\.

CREATE INDEX index_persona_followers_import_on_service_id ON persona_followers_import(service_id, follower_id);

service_ids := SELECT DISTINCT service_id FROM persona_followers_import;
for each service_id in service_ids:
  EXPLAIN ( ANALYZE, VERBOSE, COSTS, BUFFERS )
  INSERT INTO persona_followers(service_id, follower_id, valid_at)
    SELECT service_id, follower_id, MIN(valid_at)
    FROM persona_followers_import
    WHERE follower_id IS NOT NULL
      AND follower_id NOT IN (SELECT follower_id FROM persona_followers WHERE service_id = :service_id)
      AND service_id = :service_id
    GROUP BY service_id, follower_id
    ORDER BY follower_id

This seems to give me the best possible throughput. I was able to import days of data in an hour, compared to hours of work for one day of data.

Thanks for all suggestions, and PostgreSQL rocks!
François Beausoleil


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux