pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Manfred Koizar <mkoi-pg@aon.at>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] can i make this sql query more efficiant?
Date: Sat, 05 Apr 2003 04:08:22 +0200
Message-ID: <r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.com> (raw)
In-Reply-To: <200304041126.14530.josh@agliodbs.com>
References: <1049403724.13799.5473.camel@camel>
	<200304040816.01369.josh@agliodbs.com>
	<d0ir8vgn2sp6uh2h61nkm4tdasqqn4o18l@4ax.com>
	<200304041126.14530.josh@agliodbs.com>

On Fri, 4 Apr 2003 11:26:14 -0800, Josh Berkus <josh@agliodbs.com>
wrote:
>For your example, how do the statistics change if you increase the number of 
>levels to 15 and put an index on them?

CREATE TABLE baz (event int, level int);

INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5);
INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5) FROM baz;
...
INSERT INTO baz SELECT (100*random()+0.5), (15*random()+0.5) FROM baz;
ANALYSE baz;
CREATE INDEX baz_e ON baz(event);
CREATE INDEX baz_l ON baz(level);
CREATE INDEX baz_el ON baz(event, level);
CREATE INDEX baz_le ON baz(level, event);

tup  cluster     case          subsel
  8K    -     1219.90 msec   70605.93 msec (seq scan)
  8K    -                     3087.30 msec (seq scan off)

 16K    -     3861.87 msec  161902.36 msec (seq scan)
 16K    -                    31498.76 msec (seq scan off)
 16K  event   2407.72 msec    5773.12 msec
 16K  level   2298.08 msec   32752.43 msec
 16K  l, e    2318.60 msec    3184.84 msec

 32K    -     6571.57 msec    7381.22 msec
 32K  e, l    4584.97 msec    3429.94 msec
 32K  l, e    4552.00 msec   64782.59 msec
 32K  l, e    4552.98 msec    3544.32 msec (baz_l dropped)

 64K    -    17275.73 msec   26525.24 msec
 64K    -    17150.16 msec   26195.87 msec (baz_le dropped)
 64K    -    17286.29 msec  656046.24 msec (baz_el dropped)
 64K  e, l    9137.88 msec   21809.52 msec
 64K  e, l    9183.25 msec    6412.97 msec (baz_e dropped)
 64K  e, l   11690.28 msec   10022.44 msec (baz_el dropped)
 64K  e, l   11740.54 msec  643046.39 msec (baz_le dropped)
 64K  l, e    9437.65 msec  133368.20 msec
 64K  l, e    9119.48 msec    6722.00 msec (baz_l dropped)
 64K  l, e    9294.68 msec    6663.15 msec (baz_le dropped)
 64K  l, e    9259.35 msec  639754.27 msec (baz_el dropped)

256K    -    59809.69 msec  120755.78 msec
256K    -    59809.69 msec  114133.34 msec (baz_le dropped)
256K  e, l   38506.41 msec   88531.54 msec
256K  e, l   49427.43 msec   43544.03 msec (baz_e dropped)
256K  l, e   56821.23 msec  575850.14 msec
256K  l, e   57462.78 msec   67911.41 msec (baz_l dropped)

So yes, there are cases where subselect is faster than case, but case
is much more robust regarding correlation and indices.
 
Servus
 Manfred




view thread (8+ messages)

Message-ID: <r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.com>
Permalink:  ../r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.com/
Also on:    postgresql.org/message-id/r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mkoi-pg@aon.at, josh@agliodbs.com, pgsql-performance@postgresql.org
  Subject: Re: [SQL] can i make this sql query more efficiant?
  In-Reply-To: <r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.com>
  References: <1049403724.13799.5473.camel@camel> <200304040816.01369.josh@agliodbs.com> <d0ir8vgn2sp6uh2h61nkm4tdasqqn4o18l@4ax.com> <200304041126.14530.josh@agliodbs.com> <r1es8vco17r3dmoq092aqlv95qd4273p0u@4ax.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