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

multi-layered view join performance oddities

From: Svenne Krap <svenne(at)krap(dot)dk>
To: PgSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: multi-layered view join performance oddities
Date: 2005-10-30 17:16:04
Message-ID: 4364FFD4.2020801@krap.dk (view raw or flat)
Thread:
Lists: pgsql-performance
Hi there.

I have tried to implement the layered views as suggested earlier on one 
of the simplest queries (just to get a feel for it). And there seems to 
be something odd going on.

Attached are all the statemens needed to see, how the database is made 
and the contents of postgresql.conf and two explain analyzes:

The machine is a single cpu Xeon, with 2G of memory and 2 scsi-drives in 
a mirror (is going to be extended to 6 within a few weeks) running 
8.1beta3.  The whole database has been vacuum analyzed just before the 
explain analyzes.

I have spend a few hours fiddling around with the performance of it, but 
seems to go nowhere - I might have become snowblind and missed something 
obvious though.

There are a few things, that strikes me:
- the base view (ord_result_pct) is reasonable fast (41 ms) - it does a 
lot of seq scans, but right now there are not enough data there to do 
otherwise
- the pretty version (for output) is 17,5 times slower (722ms) even 
though it just joins against three tiny tables ( < 100 rows each) and 
the plan seems very different
- the slow query (the _pretty) has lower expected costs as the other ( 
338 vs 487 "performance units") , this looks like some cost parameters 
need tweaking. I cannot figure out which though.
- the top nested loop seems to eat most of the time, I have a little 
trouble seeing what this nested loop is doing there anyways

Thanks in advance

Svenne

Attachment: postgresql-perfomance-oddities.txt
Description: text/plain (9.4 KB)

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2005-10-30 17:44:50
Subject: Re: multi-layered view join performance oddities
Previous:From: Bruce MomjianDate: 2005-10-30 14:10:49
Subject: Re: Effects of cascading references in foreign keys

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