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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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 Content-Type Size
postgresql-perfomance-oddities.txt text/plain 9.4 KB

Responses

Browse pgsql-performance by date

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