From: Mario Splivalo <msplival@jagor.srce.hr>
To: Dave Dutcher <dave@tridecap.com>
To: pgsql-performance@postgresql.org
Subject: Re: Lot'sa joins - performance tip-up, please?
Date: Thu, 04 May 2006 16:15:13 +0200
Message-ID: <1146752113.8538.22.camel@localhost.localdomain> (raw)
In-Reply-To: <003001c66ec5$205a0160$8300a8c0@tridecap.com>
References: <003001c66ec5$205a0160$8300a8c0@tridecap.com>
On Wed, 2006-05-03 at 10:20 -0500, Dave Dutcher wrote:
> > -> Nested Loop (cost=0.00..176144.30 rows=57925 width=26)
> > (actual time=68.322..529472.026 rows=57925 loops=1)
> > -> Seq Scan on ticketing_codes_played
> > (cost=0.00..863.25 rows=57925 width=8) (actual time=0.042..473.881
> > rows=57925 loops=1)
> > -> Index Scan using ticketing_codes_pk on
> > ticketing_codes (cost=0.00..3.01 rows=1 width=18) (actual
> > time=9.102..9.108 rows=1 loops=57925)
> > Index Cond: (ticketing_codes.code_id =
> > "outer".code_id)
> > Total runtime: 542000.093 ms
> > (27 rows)
> >
> >
> > I'll be more than happy to provide any additional information
> > that I may
> > be able to gather. I'd be most happy if someone would scream something
> > like "four joins, smells like a poor design" because design
> > is poor, but
> > the system is in production, and I have to bare with it.
>
>
> It looks like that nested loop which is joining ticketing_codes_played
> to ticketing_codes is the slow part. I'm curious how many rows are in
> the ticketing_codes table?
>
> Four or five joins does not seem like a lot to me, but it can be slow if
> you are joining big tables with other big tables.
Ticketing_codes table has 11000000 records, and it's expected to grow.
I tried playing with JOIN order as Tom suggested, but performance is the
same.
Mario
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: msplival@jagor.srce.hr, dave@tridecap.com
Subject: Re: Lot'sa joins - performance tip-up, please?
In-Reply-To: <1146752113.8538.22.camel@localhost.localdomain>
* 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