pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
To: pgsql-performance@postgresql.org
Subject: Large CASE-statement is pretty slow?
Date: Sun, 14 Mar 2004 20:33:00 +0100
Message-ID: <4054B36C.8030306@vulcanus.its.tudelft.nl> (raw)

Hi list,

I was more or less toying with an idea for a project I have, which 
includes renumbering a primary key (don't ask, it's necessary :/ )

Anyway, I was looking into the usefullness of a INSERT INTO newtable 
SELECT field, field, CASE pkey WHEN x1 THEN y1 WHEN x2 THEN y2 etc END 
FROM oldtable

The resulting select was about 1.7MB of query-text, mostly composed of 
the CASE-statement. So I discarded that idea, I still wanted to know how 
much time it would take on my database (MySQL) and found it to take 
about 1100 seconds, in contrast to simply selecting the data, which'd 
take about 0.7 seconds orso... The table I tested this on is about 30MB.

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.

What I'm wondering about:
Although I was not expecting Postgresql to heavily beat MySQL, I was 
surprised to see it so much slower. Is the CASE-statement in Postgresql 
that inefficient? Or is it simply not very scalable (i.e. don't try to 
have 100000 cases like I did)?

The database is a lightly optimised gentoo-compile of 7.4.2, the 
mysql-version was 4.0.18 in case anyone wanted to know that.


Best regards,

Arjen van der Meijden


PS, don't try to "help improve the query" I discarded the idea as too 
inefficient and went along with a simple left join to get the "new pkey" 
out of a temporary table ;)





view thread (7+ messages)  latest in thread

Message-ID: <4054B36C.8030306@vulcanus.its.tudelft.nl>
Permalink:  ../4054B36C.8030306@vulcanus.its.tudelft.nl/
Also on:    postgresql.org/message-id/4054B36C.8030306@vulcanus.its.tudelft.nl

 · 

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: acmmailing@vulcanus.its.tudelft.nl
  Subject: Re: Large CASE-statement is pretty slow?
  In-Reply-To: <4054B36C.8030306@vulcanus.its.tudelft.nl>

* 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