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

Re: Materializing a sequential scan

From: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Materializing a sequential scan
Date: 2005-10-26 21:37:30
Message-ID: 20051026213730.GA2295@uio.no (view raw or flat)
Thread:
Lists: pgsql-performance
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/

In response to

Responses

pgsql-performance by date

Next:From: PFCDate: 2005-10-26 21:49:54
Subject: Re: browsing table with 2 million records
Previous:From: Tom LaneDate: 2005-10-26 21:31:57
Subject: Re: browsing table with 2 million records

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