X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 83160D9B9E for ; Sat, 15 Oct 2005 11:48:57 -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 82518-02 for ; Sat, 15 Oct 2005 14:48:51 +0000 (GMT) Received: from fmf.vtu.lt (fmf.vtu.lt [193.219.145.206]) by svr1.postgresql.org (Postfix) with ESMTP id 106DDD9B85 for ; Sat, 15 Oct 2005 11:48:54 -0300 (ADT) Received: from fmf.vtu.lt (localhost [127.0.0.1]) by fmf.vtu.lt (Postfix) with ESMTP id 2F51818473; Sat, 15 Oct 2005 17:48:54 +0300 (EEST) Received: from 81.7.89.65 (SquirrelMail authenticated user nso) by fmf.vtu.lt with HTTP; Sat, 15 Oct 2005 17:48:54 +0300 (EEST) Message-ID: <17532.81.7.89.65.1129387734.squirrel@fmf.vtu.lt> In-Reply-To: References: <17363.81.7.89.65.1129384809.squirrel@fmf.vtu.lt> Date: Sat, 15 Oct 2005 17:48:54 +0300 (EEST) Subject: Re: Bytea poor performance From: "NSO" To: "Sean Davis" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.5 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-VGTU-MailScanner-OpenProtect-Information: Please contact the ISP for more information X-VGTU-MailScanner-OpenProtect: Found to be clean X-MailScanner-OpenProtect-MCPCheck: X-MailScanner-OpenProtect-From: nso@fmf.vtu.lt X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.27 required=5 tests=[AWL=-0.104, DNS_FROM_RFC_ABUSE=0.374] X-Spam-Level: X-Archive-Number: 200510/242 X-Sequence-Number: 15010 Yes, it takes even up to 35 seconds.. I did the same query on the server (not PC with was connected directly to server with 100mbit net), and /I got better result it is 3.5 - 4 seconds, but it still not good.. Why it is slow? and why the difference is so big? I mean from 4 to 35 seconds? thx > On 10/15/05 10:00 AM, "NSO" wrote: > >> Hello, >> >> How about some explain analyze output? >> Explain analyse select * from files where nr > 1450 >> >> "Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >> (actual time=0.000..0.000 rows=50 loops=1)" > > I may not be understanding the output, but your actual time reports 0 for > the query. And the total runtime is 23 seconds? > > Sean > > >>> On 10/15/05 9:20 AM, "NSO" wrote: >>> >>>> Hello, >>>> >>>> I am trying to select form table with bytea field. And queries runs >>>> very >>>> slow. >>>> My table: >>>> CREATE TABLE files (file bytea, nr serial NOT NULL) WITH OIDS; >>>> >>>> Query: >>>> select * from files where nr > 1450 >>>> >>>> (I have total 1500 records in it, every holds picture of 23kB size) >>>> Query runs very long: >>>> Total query runtime: 23625 ms. >>>> Data retrieval runtime: 266 ms. >>>> 50 rows retrieved. >>>> >>>> explain: >>>> Index Scan using pk on files (cost=0.00..3.67 rows=50 width=36) >>>> Index Cond: (nr > 1450) >>>> >>>> Is it possible to do something with it? or it is normal? Our server is >>>> fast, and all other tables work fine.. >>> >>> How about some explain analyze output? Have you done a full vacuum >>> lately? >>> How about reindexing? >>> >>> Sean >>> >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: explain analyze is your friend >>> >>> -- >>> This message has been scanned for viruses and >>> dangerous content, and is believed to be clean. >>> >>> >> >> > > > -- > This message has been scanned for viruses and > dangerous content, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content, and is believed to be clean.