Date: Wed, 26 Oct 2005 23:37:30 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Materializing a sequential scan
Message-ID: <20051026213730.GA2295@uio.no>
Mail-Followup-To: pgsql-performance@postgresql.org
References: <20051019174544.GA32303@samfundet.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <20051019174544.GA32303@samfundet.no>
User-Agent: Mutt/1.5.11

Hi,

I finally found what I believe is the root cause for the hopeless
performance, after a lot of query rewriting:

>  Subquery Scan mdb_effektiv_tilgang  (cost=19821.69..4920621.69 rows=10000 width=48)
>    Filter: ((NOT (hashed subplan)) AND (NOT (subplan)))

The problem here is simply that 8.1 refuses to hash this part of the plan:

>      ->  Materialize  (cost=546.45..742.37 rows=19592 width=38)
>            ->  Seq Scan on rita_tilgang  (cost=0.00..526.86 rows=19592 width=38)
>      ->  Seq Scan on personer_nylig_slettet  (cost=0.00..31.40 rows=2140 width=4)

probably because of the NOT IN with a function inside; I rewrote it to an
EXCEPT (which is not equivalent, but good enough for my use), and it
instantly hashed the other subplan, and the query went speedily. Well, at
least in four seconds and not several hours...

Any good ideas why 8.1 would refuse to do this, when 7.4 would do it? It does
not matter how high I set my work_mem; even at 2.000.000 it refused to hash
the subplan.

/* Steinar */
-- 
Homepage: http://www.sesse.net/