7.4 beta 1 getting out of swap - Mailing list pgsql-performance

    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