Re: [SQL] can i make this sql query more efficiant?

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