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

Re: BUG #1552: massive performance hit between 7.4 and 8.0.1

From: Andrew - Supernews <andrew+nonews(at)supernews(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1
Date: 2005-03-24 09:14:25
Message-ID: slrnd4517h.rhd.andrew+nonews@trinity.supernews.net (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-performance
On 2005-03-23, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> No, it wouldn't, because by the time you do the first FK trigger you'd
> have one row/one page in the referenced table, so it'd still look like a
> seqscan situation to the planner.  The only way we could make that work
> is to effectively disable seqscans entirely, by *always* pretending the
> table size is large enough to trigger an indexscan, even when the
> planner can plainly see that it's not.  This is not an acceptable answer
> IMHO.

I'm not yet convinced the planner is right to _ever_ choose a seqscan for
FK triggers. The idea that a seqscan is faster on small tables is
traditional, and it has some justification in the case where nothing is
in the cache (since index scan will touch the disk twice in that case),
but I'm finding that for tables of the order of 50 rows (easily fitting in
one page) that index scans are as fast as or faster than seqscans for
doing simple one-row lookups provided the tables are in cache.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

In response to

pgsql-performance by date

Next:From: Patrick VedrinesDate: 2005-03-24 09:33:50
Subject: Re: CPU 0.1% IOWAIT 99% for decisonnal queries
Previous:From: Josh BerkusDate: 2005-03-23 23:39:34
Subject: Re: PostgreSQL on Solaris 8 and ufs

pgsql-bugs by date

Next:From: Roy BadamiDate: 2005-03-24 10:15:44
Subject: Re: BUG #1517: SQL interval syntax is accepted by the parser,
Previous:From: Bruce MomjianDate: 2005-03-24 03:49:48
Subject: Re: BUG #1518: Conversions to (undocumented) SQL year-month and

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