Re: how to improve perf of 131MM row table? - Mailing list 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.
>