Message-ID: <53AC3327.1060303@comcast.net>
Date: Thu, 26 Jun 2014 10:50:15 -0400
From: AJ Weber <aweber@comcast.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: Shaun Thomas <sthomas@optionshouse.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
References: <53AB2CB1.8080201@comcast.net> <53AB35CC.10202@optionshouse.com>
 <1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
 <53AB4551.4070207@optionshouse.com> <53AC1F6E.4000700@comcast.net>
 <53AC28AF.1060500@optionshouse.com> <53AC2CC3.9060403@comcast.net>
 <53AC3024.5040409@optionshouse.com>
In-Reply-To: <53AC3024.5040409@optionshouse.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

 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.
>


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance