X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A016252930 for ; Tue, 9 Aug 2005 02:05:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33565-06 for ; Tue, 9 Aug 2005 05:05:44 +0000 (GMT) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id 2EACC52928 for ; Tue, 9 Aug 2005 02:05:43 -0300 (ADT) Received: from smtp-3.paradise.net.nz (smtp-3a.paradise.net.nz [202.0.32.196]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IKX00NR7VHI1I@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Tue, 09 Aug 2005 17:05:42 +1200 (NZST) Received: from [192.168.1.11] (218-101-13-165.paradise.net.nz [218.101.13.165]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id F35C5AE067; Tue, 09 Aug 2005 17:05:41 +1200 (NZST) Date: Tue, 09 Aug 2005 17:05:39 +1200 From: Mark Kirkwood Subject: Re: QRY seems not using indexes In-reply-to: To: morandell@gmail.com Cc: Qingqing Zhou , pgsql-performance@postgresql.org Message-id: <42F839A3.2080501@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726) References: <1123071402.641229.314250@g43g2000cwa.googlegroups.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.152 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/74 X-Sequence-Number: 13821 Qingqing Zhou wrote: > writes > >> >>so, if I do a qry like "EXPLAIN ANALYZE select * from pridecdr where >>idsede=8977758488" it tooks a lot of time before i get back any result: >> >>Index Scan using prd_id_sede on pridecdr (cost=0.00..699079.90 >>rows=181850 width=138) (actual time=51.241..483068.255 rows=150511 >>loops=1) >> Index Cond: (idsede = 8977758488::bigint) >> Total runtime: 483355.325 ms >> > > > The query plan looks ok. Try to do EXPLAIN ANALYZE twice and see if there is > any difference. This could reduce the IO time to read your index/data since > you got enough RAM. > > Also, if you haven't done VACUUM FULL for a long time, do so and compare the > difference. > Could also be libpq buffering all 150000 rows before showing any. It might be worthwhile using a CURSOR and doing 1 FETCH. If that is quick, then buffering is probably the issue. BTW - do you really want all the rows? Cheers Mark