pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: Large CASE-statement is pretty slow?
Date: 15 Mar 2004 12:15:45 -0500
Message-ID: <87y8q22fr2.fsf@stark.xeocode.com> (raw)
In-Reply-To: <4054B36C.8030306@vulcanus.its.tudelft.nl>
References: <4054B36C.8030306@vulcanus.its.tudelft.nl>

Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> writes:

> 
> Of course I wanted to know how long it'd take on postgresql, selecting the
> pkey-field only (without the case) took also some 0.7 seconds (the entire table
> may have been more).
> But the CASE-version took 9026139.201 ms, i.e. over 9000 seconds about 8 times
> slower than MySQL.

Was this the select with the CASE, or the update?

If you did the update and have lots of foreign key references to the table
then every record that's updated forces a check to make sure there are no
references to that record (or an update if it's ON UPDATE CASCADE). If there
are no indexes on the referencing table columns that will be very slow.

-- 
greg




view thread (7+ messages)  latest in thread

Message-ID: <87y8q22fr2.fsf@stark.xeocode.com>
Permalink:  ../87y8q22fr2.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87y8q22fr2.fsf@stark.xeocode.com

 · 

reply

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: gsstark@mit.edu, acmmailing@vulcanus.its.tudelft.nl
  Subject: Re: Large CASE-statement is pretty slow?
  In-Reply-To: <87y8q22fr2.fsf@stark.xeocode.com>

* 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