Re: how to improve perf of 131MM row table?
От | AJ Weber |
---|---|
Тема | Re: how to improve perf of 131MM row table? |
Дата | |
Msg-id | 53AC3327.1060303@comcast.net обсуждение исходный текст |
Ответ на | Re: how to improve perf of 131MM row table? (Shaun Thomas <sthomas@optionshouse.com>) |
Список | pgsql-performance |
From psql (same session as previous \d output) -- Hash Join (cost=328182.35..548154.83 rows=52790 width=187) (actual time=4157.886..4965.466 rows=1071 loops=1) Hash Cond: (prop.node_id = node.id) Buffers: shared hit=146711 read=23498, temp read=23676 written=23646 -> Bitmap Heap Scan on alf_node_properties prop (cost=1253.19..189491.88 rows=52790 width=179) (actual time=0.429..1.154 rows=1071 loops=1) Recheck Cond: (node_id = ANY ('{175769,175771,175781,175825,175881,175893,175919,175932,175963,175999,176022,176079,176099,176115,176118,176171,176181,176217,176220,176243,176283,176312,176326,176335,176377,176441,176444,176475,176530,176570,176623,176674,176701,176730,176748,176763,176771,176808,176836,176851,176864,176881,176929,176945,176947,176960,177006,177039,177079,177131,177144}'::bigint[])) Buffers: shared hit=278 -> Bitmap Index Scan on fk_alf_nprop_n (cost=0.00..1240.00 rows=52790 width=0) (actual time=0.411..0.411 rows=1071 loops=1) Index Cond: (node_id = ANY ('{175769,175771,175781,175825,175881,175893,175919,175932,175963,175999,176022,176079,176099,176115,176118,176171,176181,176217,176220,176243,176283,176312,176326,176335,176377,176441,176444,176475,176530,176570,176623,176674,176701,176730,176748,176763,176771,176808,176836,176851,176864,176881,176929,176945,176947,176960,177006,177039,177079,177131,177144}'::bigint[])) Buffers: shared hit=207 -> Hash (cost=227265.29..227265.29 rows=5733429 width=16) (actual time=4156.075..4156.075 rows=5734255 loops=1) Buckets: 65536 Batches: 16 Memory Usage: 16888kB Buffers: shared hit=146433 read=23498, temp written=23609 -> Seq Scan on alf_node node (cost=0.00..227265.29 rows=5733429 width=16) (actual time=0.004..1908.493 rows=5734255 loops=1) Buffers: shared hit=146433 read=23498 Total runtime: 4967.674 ms (15 rows) On 6/26/2014 10:37 AM, Shaun Thomas wrote: > On 06/26/2014 09:22 AM, AJ Weber wrote: > >> I sent the details as identified by pgAdmin III. > > Interesting. Either there is a bug in pgAdmin, or you're connecting to > a different database that is missing the primary key. What is the > EXPLAIN ANALYZE output if you execute the query you sent on a psql > prompt? > >> "alf_node_pkey" PRIMARY KEY, btree (id) CLUSTER >> would indicate to me that there is a PK on alf_node table, it is on >> column "id", it is of type btree, and the table is clustered around that >> index. >> >> Am I reading this totally wrong? > > No, that's right. But that wasn't in the SQL you sent. In fact, > there's a lot of stuff missing in that output. > > Try running the EXPLAIN ANALYZE using the same psql connection you > used to retrieve the actual table structure just now. I suspect you've > accidentally connected to the wrong database. If it's still doing the > sequence scan, we'll have to dig deeper. >
В списке pgsql-performance по дате отправления: