Re: Most efficient way to insert without duplicates

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(at)teksol(dot)info>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(at)teksol(dot)info
> > 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
>