Skip site navigation (1) Skip section navigation (2)

Re: Question about SQL statement error

From: Marco Colombo <marco(at)esi(dot)it>
To: Mário Gamito <gamito(at)netual(dot)pt>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Question about SQL statement error
Date: 2004-08-25 15:01:17
Message-ID: Pine.LNX.4.61.0408251652350.2465@Megathlon.ESI (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, 25 Aug 2004, [ISO-8859-1] Mário Gamito wrote:

> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.
>
> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;
>
> Any help would be appreciated.
>
> Sorry for the basic answer :(
>
> Warm Regards,
> Mário Gamito
>

Check out COPY and \copy. COPY is server-side, thus it's executed on
the host the server runs on, with the permissions of the user the server
is running as. \copy is a psql command, thus client-side. It's executed
on the client host and with the permissions of the user running psql (you).
If you are writing your own client application, you can use
COPY ... FROM STDIN and COPY ... TO STDOUT (which is what psql does).

http://www.postgresql.org/docs/7.4/interactive/sql-copy.html
http://www.postgresql.org/docs/7.4/interactive/app-psql.html

.TM.
-- 
       ____/  ____/   /
      /      /       /			Marco Colombo
     ___/  ___  /   /		      Technical Manager
    /          /   /			 ESI s.r.l.
  _____/ _____/  _/		       Colombo(at)ESI(dot)it
>From pgsql-general-owner(at)postgresql(dot)org  Wed Aug 25 22:51:37 2004
X-Original-To: pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8BE195E37D0
	for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Wed, 25 Aug 2004 22:51:36 -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 36014-01
 for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
 Thu, 26 Aug 2004 01:51:31 +0000 (GMT)
Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55])
	by svr1.postgresql.org (Postfix) with ESMTP id 7978A5E3632
	for <pgsql-general(at)postgresql(dot)org>; Wed, 25 Aug 2004 22:51:28 -0300 (ADT)
Received: from kenny.ugholf.net ([67.161.200.254])
          by comcast.net (sccrmhc11) with ESMTP
          id <2004082601513301100c3tmhe>; Thu, 26 Aug 2004 01:51:33 +0000
Received: from jandr.org (rjo-200-170-42-140.dial-up.vento.com.br [200.170.42.140] (may be forged))
	(authenticated bits=0)
	by kenny.ugholf.net (8.12.10/8.12.10) with ESMTP id i7Q1pNtv029795
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-general(at)postgresql(dot)org>; Wed, 25 Aug 2004 19:51:29 -0600
Message-ID: <412D431F(dot)2050908(at)jandr(dot)org>
Date: Wed, 25 Aug 2004 22:55:43 -0300
From: Jon Lapham <lapham(at)jandr(dot)org>
Reply-To: lapham(at)jandr(dot)org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-general(at)postgresql(dot)org
Subject: EXPLAIN ANALYZE total runtime != walltime
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0
 tests=RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL
X-Spam-Level: *
X-Archive-Number: 200408/1335
X-Sequence-Number: 64890

I have been using the EXPLAIN ANALYZE command to debug some performance 
bottlenecks in my database.  In doing so, I have found an oddity (to me 
anyway).  The "19ms" total runtime reported below actually takes 25 
seconds on my computer (no other CPU intensive processes running).  Is 
this normal for EXPLAIN ANALYZE to report a total runtime so vastly 
different from wall clock time?

During the "explain ANALYZE delete from msgid;" the CPU is pegged at 
100% for the postmaster process, and the HD light only flashes 
intermittently, so I do not think it is HD I/O.

I tossed in a "EXPLAIN ANALYZE VERBOSE" at the end of this email, in 
case that helps anyone.

Thanks for any help!
-Jon

translator=> SELECT version();
                                                              version 
 

---------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC 
i386-redhat-linux-gcc (GCC) 3.3.3 20040216 (Red Hat Linux 3.3.3-2.1)
(1 row)

translator=> VACUUM ANALYZE ;
WARNING:  skipping "pg_shadow" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_database" --- only table or database owner can 
vacuum it
WARNING:  skipping "pg_group" --- only table or database owner can vacuum it
VACUUM
translator=> BEGIN ;
BEGIN
translator=> explain ANALYZE delete from msgid;
                                              QUERY PLAN 

-----------------------------------------------------------------------------------------------------
  Seq Scan on msgid  (cost=0.00..23.81 rows=981 width=6) (actual 
time=0.029..10.151 rows=981 loops=1)
  Total runtime: 19.755 ms
(2 rows)
translator=> \d msgid
                                Table "public.msgid"
   Column   |     Type      |                       Modifiers 

-----------+---------------+-------------------------------------------------------
  id        | integer       | not null default 
nextval('public.msgid_id_seq'::text)
  projectid | integer       | not null
  msgid     | text          | not null
  msgidmd5  | character(32) | not null
Indexes:
     "msgid_pkey" primary key, btree (id)
Foreign-key constraints:
     "$1" FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE CASCADE
translator=> rollback;
ROLLBACK
translator=> explain ANALYZE VERBOSE delete from msgid;
                                              QUERY PLAN

----------------------------------------------------------------------------------------------------
     {SEQSCAN
     :startup_cost 0.00
     :total_cost 23.81
     :plan_rows 981
     :plan_width 6
     :targetlist (
        {TARGETENTRY
        :resdom
           {RESDOM
           :resno 1
           :restype 27
           :restypmod -1
           :resname ctid
           :ressortgroupref 0
           :resorigtbl 0
           :resorigcol 0
           :resjunk true
           }
        :expr
           {VAR
           :varno 1
           :varattno -1
           :vartype 27
           :vartypmod -1
           :varlevelsup 0
           :varnoold 1
           :varoattno -1
           }
        }
     )

     :qual <>
     :lefttree <>
     :righttree <>
     :initPlan <>
     :extParam ()

     :allParam ()

     :nParamExec 0
     :scanrelid 1
     }

  Seq Scan on msgid  (cost=0.00..23.81 rows=981 width=6) (actual 
time=0.031..6.444 rows=981 loops=1)
  Total runtime: 35.760 ms
(46 rows)


-- 
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
  Jon Lapham  <lapham(at)jandr(dot)org>                Rio de Janeiro, Brasil
  Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------


In response to

pgsql-general by date

Next:From: Greg DonaldDate: 2004-08-25 15:01:22
Subject: Re: Gentoo for production DB server?
Previous:From: Chris TraversDate: 2004-08-25 14:56:39
Subject: Re: copy a database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group