From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Materializing a sequential scan
Date: Wed, 26 Oct 2005 23:37:30 +0200
Message-ID: <20051026213730.GA2295@uio.no> (raw)
In-Reply-To: <20051019174544.GA32303@samfundet.no>
References: <20051019174544.GA32303@samfundet.no>
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/
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgunderson@bigfoot.com
Subject: Re: Materializing a sequential scan
In-Reply-To: <20051026213730.GA2295@uio.no>
* 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