pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bertrand Petit <pgsql@phoe.frmug.org>
To: pgsql-performance@postgresql.org
Subject: 7.4 beta 1 getting out of swap
Date: Thu, 14 Aug 2003 12:21:30 +0200
Message-ID: <20030814122130.A3583@memo.frmug.org> (raw)


	Hello,

	I recently switched to 7.4 beta 1, one query that used to be
corectly executed uder 7.3.3 albeit slowly now abnormaly ends when the
backend can't get more memory when it ate about 480 MB of swap space.
I suspect that this behavior is the result of a 7.4 beta 1 bug but I
wanted to be sure it is one before posting a report on pgsql-bugs.

	That query operates on three tables:

==============================================================================
db=> \d movies
                              Table "public.movies"
   Column   |       Type        |                    Modifiers                    
------------+-------------------+-------------------------------------------------
 id         | bigint            | not null default nextval('movies_id_seq'::text)
 title      | character varying | not null
 orig_title | character varying | not null
 year       | smallint          | 
 year_end   | smallint          | 
Indexes:
    "movies_id_idx" unique, btree (id)
    "movies_title_idx" unique, btree (title)
    "movies_orig_title_idx" btree (orig_title)
    "movies_year_idx" btree ("year")
Check constraints:
    "movies_year" CHECK (("year" >= 1888) OR ("year" IS NULL))
    "$1" CHECK ((year_end >= 1888) OR (year_end IS NULL))

db=> \d raw_atitles
                            Table "public.raw_atitles"
   Column   |        Type         |                    Modifiers                     
------------+---------------------+--------------------------------------------------
 main_title | character varying   | not null
 aka_title  | character varying   | 
 charset    | character varying   | not null default 'ISO-8859-1'::character varying
 byte_title | character varying   | not null
 attribs    | character varying[] | 
Indexes:
    "rimdb_atitles_aka_title_idx" btree (aka_title)
    "rimdb_atitles_attribs_idx" btree (attribs array_ops)
    "rimdb_atitles_main_title_idx" btree (main_title)

db=> \d atitles
            Table "public.atitles"
   Column   |        Type         | Modifiers 
------------+---------------------+-----------
 title      | character varying   | not null
 movie_id   | bigint              | not null
 attribs    | character varying[] | 
 orig_title | character varying   | not null
Indexes:
    "truc" unique, btree (movie_id, orig_title, attribs array_ops)
    "atitles_movie_id_idx" btree (movie_id)
    "atitles_title_idx" btree (title)
Foreign-key constraints:
    "$1" FOREIGN KEY (movie_id) REFERENCES movies(id)

==============================================================================

	The operation is to update the "core" atitles table with the
contents of the "raw" raw_atitles table. The query is as follows:

==============================================================================
INSERT INTO atitles (movie_id, title, attribs, orig_title)
  SELECT mo.id, trans_title(rak.aka_title), rak.attribs, rak.aka_title
  FROM movies AS mo, raw_atitles AS rak 
  WHERE mo.orig_title=rak.main_title AND 
  NOT EXISTS
    (SELECT at2.movie_id from atitles AS at2
     WHERE at2.movie_id=mo.id AND
     at2.orig_title=rak.aka_title AND 
     at2.attribs=rak.attribs);
==============================================================================

	Table sizes are 362,921 rows for movies, 152,549 for atitles,
and 160,114 for raw_atitles.

	The query plan is:

==============================================================================
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Merge Join  (cost=106998.67..1039376.63 rows=80057 width=86)
   Merge Cond: ("outer"."?column3?" = "inner"."?column4?")
   Join Filter: (NOT (subplan))
   ->  Sort  (cost=66212.69..67119.99 rows=362921 width=38)
         Sort Key: (mo.orig_title)::text
         ->  Seq Scan on movies mo  (cost=0.00..8338.21 rows=362921 width=38)
   ->  Sort  (cost=40785.99..41186.27 rows=160114 width=107)
         Sort Key: (rak.main_title)::text
         ->  Seq Scan on raw_atitles rak  (cost=0.00..5145.14 rows=160114 width=107)
   SubPlan
     ->  Index Scan using truc on atitles at2  (cost=0.00..5.80 rows=1 width=8)
           Index Cond: ((movie_id = $0) AND ((orig_title)::text = ($1)::text) AND (attribs = $2))
(12 rows)

==============================================================================

	I suspect that the backend does not comply to the sort_mem
parameter (set to the default 1024).

	So my question is: does this really looks like a bug?

	Regards.

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage



view thread (9+ messages)  latest in thread

Message-ID: <20030814122130.A3583@memo.frmug.org>
Permalink:  ../20030814122130.A3583@memo.frmug.org/
Also on:    postgresql.org/message-id/20030814122130.A3583@memo.frmug.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pgsql@phoe.frmug.org
  Subject: Re: 7.4 beta 1 getting out of swap
  In-Reply-To: <20030814122130.A3583@memo.frmug.org>
  References: <20030814122130.A3583@memo.frmug.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox