Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50765=pgsql-performance-ar@postgresql.org>)
 id 1VfzUh-0006Vl-3j
 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2013 21:58:03 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50765=pgsql-performance-ar@postgresql.org>)
 id 1VfzUg-0006DA-9w
 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2013 21:58:02 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VfzUc-00066L-Eq
 for pgsql-performance@postgresql.org; Mon, 11 Nov 2013 21:57:58 +0000
Received: from exprod5og107.obsmtp.com ([64.18.0.184])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1VfzUY-0005Fa-LO
 for pgsql-performance@postgresql.org; Mon, 11 Nov 2013 21:57:57 +0000
Received: from mail-ie0-f179.google.com ([209.85.223.179]) (using TLSv1) by
 exprod5ob107.postini.com ([64.18.4.12]) with SMTP
 ID DSNKUoFS4S67qPEDLPfuYG40vKiyuBVi+zsm@postini.com;
 Mon, 11 Nov 2013 13:57:54 PST
Received: by mail-ie0-f179.google.com with SMTP id u16so5449150iet.10
 for <pgsql-performance@postgresql.org>; Mon, 11 Nov 2013 13:57:50 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enova.com; s=google;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=ogHHJF6TCi+bO1T8tvrEK+EVPnNCw6ucok0gS053SqU=;
 b=Pa27oywT2Yj9Di5C9ky4j95HvLAgqM36cCADEh8Pg0EHH0hXrNjmPFMMoIEbZ2eNH+
 URZdtt4itfUwo8EBnCYfnuojFdItjufJihRLC3fZZlM3HJBAfI2rYM/nZMgeStA4Y2JL
 U443i9z//CVJITv5atXL2Veffr8SWboSUwfFo=
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to
 :cc:subject:references:in-reply-to:content-type
 :content-transfer-encoding;
 bh=ogHHJF6TCi+bO1T8tvrEK+EVPnNCw6ucok0gS053SqU=;
 b=lIrMqFCz4dLtV+5ImjgWrpbOoFD2dV67P364hKBtajakXnF0p7uVpTxcOjSfhGk5SO
 m/w8JFZINdm9yqiTZL5wWmcw3eOmI0fOcIagRiA3dkMODl1k9OIEUznohqKZ48PNr6UF
 yQNrRLd3SscVCKHyagsRxEN3d7jFIzDATHghRd2vEFNFQdvls7sZRVcGhA7Pkr0oZEyi
 B4WRCE3F3gshc3eG4cRoC117RRTyIfznl2QfHdAoKjc1W1FfA9vAUUzZNkNaWDk9VAcy
 OH/EYpiyk4axYbgKsJMNOYFeF0cPgXC8EAq46Qf/6UEjZYxfXH3CdiJHI0iBE3NSCWeL
 dPrA==
X-Gm-Message-State: 
 ALoCoQnBfc0wEm3y3g/hkWm12J5hNpShH7CKPj/o40j6rUG83lsuSOSHK0KsJiKUTNZOoYK+Zf3NV5diKNPP/H77iYTj823dbXRJn7vzYGoOQcQTfyHb4fkSAWM4zMUksHpIRAWlM34AsgKTE2tpI5qBgvfpuK24A3jwmX4NxeR/U/voqdt4RaCltWnkDsWl1zI3Bf/yxgKa
X-Received: by 10.50.23.16 with SMTP id i16mr13791747igf.50.1384207070655;
 Mon, 11 Nov 2013 13:57:50 -0800 (PST)
X-Received: by 10.50.23.16 with SMTP id i16mr13791743igf.50.1384207070590;
 Mon, 11 Nov 2013 13:57:50 -0800 (PST)
Received: from decina.local (rrcs-67-79-71-90.sw.biz.rr.com. [67.79.71.90])
 by mx.google.com with ESMTPSA id w4sm20909015igb.5.2013.11.11.13.57.49
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 11 Nov 2013 13:57:50 -0800 (PST)
Message-ID: <528152DB.60907@enova.com>
Date: Mon, 11 Nov 2013 15:57:47 -0600
From: Jim Nasby <jnasby@enova.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: Daniel Farina <daniel@heroku.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Horrific time for getting 1 record from an index?
References: <52815099.4080409@enova.com>
 <CAAZKuFaroDOtTj1MiZYmv9ZuHck_LQ-B2LjP_gDotqRPXtofMg@mail.gmail.com>
In-Reply-To: 
 <CAAZKuFaroDOtTj1MiZYmv9ZuHck_LQ-B2LjP_gDotqRPXtofMg@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -4.3 (----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 11/11/13 3:51 PM, Daniel Farina wrote:
> On Mon, Nov 11, 2013 at 1:48 PM, Jim Nasby <jnasby@enova.com> wrote:
>> Postgres 9.1.9.
>>
>> explain analyze select min(insert_time) from cnu_stats.page_hits_raw ;
>> I checked and there were no un-granted locks... but I have a hard time
>> believing it actually too 257 seconds to get 2 pages (one index, one heap)
>> back from our SAN.
>
> Try adding EXPLAIN (ANALYZE, BUFFERS).  I am wondering if you are
> reading through a lot of pages addressing tuples not visible to the
> transaction.

explain (analyze,buffers) select min(insert_time) from cnu_stats.page_hits_raw ;
                                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=0.12..0.13 rows=1 width=0) (actual time=119.347..119.347 rows=1 loops=1)
    Buffers: shared hit=1 read=9476
    InitPlan 1 (returns $0)
      ->  Limit  (cost=0.00..0.12 rows=1 width=8) (actual time=119.335..119.336 rows=1 loops=1)
            Buffers: shared hit=1 read=9476
            ->  Index Scan using page_hits_raw_pkey on page_hits_raw  (cost=0.00..5445004.65 rows=47165480 width=8) (actual time=119.333..119.333 rows=1 loops=1)
                  Index Cond: (insert_time IS NOT NULL)
                  Buffers: shared hit=1 read=9476
  Total runtime: 119.382 ms
(9 rows)

We do run a regular process to remove older rows... I thought we were vacuuming after that process but maybe not.
-- 
Jim Nasby, Lead Data Architect   (512) 569-9461


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