---

Jeg har et problem med et subquery, som jeg ikke kan få til at fungere
effektivt nok. Jeg har puslet lidt med det, og er kørt fast, så nu må
jeg have et tip...

Der er i PostgreSQL 8.0.0

Jeg har en tabel, som ser sådan ud:

Table "public.afles"
Column | Type | Modifiers
--------+--------------------------+-----------
sted | character(10) | not null
dato | date | not null
verdi | integer |
tid | timestamp with time zone |
op | character(10) |
Indexes:
"afles_pkey" PRIMARY KEY, btree (sted, dato)
"afles_dato_idx" btree (dato)


Det er dette query, jeg har et problem med:

select * from afles a1
where sted = 'VM200'
and dato = (
select max(dato) from afles a2
where a2.sted = a1.sted
and dato <= '2005-06-01'
);

Hvis jeg ændret mit query en lille smule går det langt hurtigere:

select * from afles a1
where sted = 'VM200'
and dato = (
select max(dato) from afles a2
where a2.sted = 'VM200'
and dato <= '2005-06-01'
);

Problemet er her, at den samme parameter indgår to gange. Jeg vil gerne
bruge funktionen i et større query, som skal blive til et view, og så
går det ikke.


En 'explain analyze' på de to querys giver følgende resultater (beklager
ombrydningen):

QUERY
PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------
Index Scan using afles_pkey on afles a1 (cost=0.00..1184.90 rows=1
width=44) (actual time=429.319..429.332 rows=1 loops=1)
Index Cond: (sted = 'VM200'::bpchar)
Filter: (dato = (subplan))
SubPlan
-> Aggregate (cost=39.01..39.01 rows=1 width=4) (actual
time=3.164..3.166 rows=1 loops=134)
-> Index Scan using afles_pkey on afles a2
(cost=0.00..38.98 rows=10 width=4) (actual time=0.108..2.636 rows=134
loops=134)
Index Cond: ((sted = $0) AND (dato <=
'2005-06-01'::date))
Total runtime: 429.934 ms


QUERY PLAN
------------------------------------------------------------------------
---------------------------------------------------------------
Index Scan using afles_pkey on afles a1 (cost=39.01..44.99 rows=1
width=44) (actual time=3.430..3.437 rows=1 loops=1)
Index Cond: ((sted = 'VM200'::bpchar) AND (dato = $0))
InitPlan
-> Aggregate (cost=39.01..39.01 rows=1 width=4) (actual
time=3.281..3.283 rows=1 loops=1)
-> Index Scan using afles_pkey on afles a2
(cost=0.00..38.98 rows=10 width=4) (actual time=0.144..2.741 rows=134
loops=1)
Index Cond: ((sted = 'VM200'::bpchar) AND (dato <=
'2005-06-01'::date))
Total runtime: 3.776 ms


Forskellen er tilsyneladende, at det ene subquery kører som en 'sub',
mens det andet kører som en 'init'. Men ellers bliver jeg ikke så meget
klogere...

--
Thorkil Olesen,
Hanstholm.

---

On Wed, 1 Jun 2005 22:34:46 +0200,
slet.dette.thorkil.og.dette@pip.dknet.dk (Thorkil Olesen) wrote:

> select * from afles a1
> where sted = 'VM200'
> and dato = (
> select max(dato) from afles a2
> where a2.sted = a1.sted
> and dato <= '2005-06-01'
> );
>
>Hvis jeg ændret mit query en lille smule går det langt hurtigere:
>
> select * from afles a1
> where sted = 'VM200'
> and dato = (
> select max(dato) from afles a2
> where a2.sted = 'VM200'
> and dato <= '2005-06-01'
> );

Hvad hvis du omformulerer til

select * from afles a1 NATURAL JOIN
(select max(dato) as dato, sted from afles a2
where dato <= '2005-06-01'
) as md
where sted = 'VM200';

Du kan evt putte sted = 'VM200' indenfor parentesen.

Jeg har ingen ide om det er hurtigere - men synes til gengaeld det er
den mest laesbare maade at goere det paa.

--
Nis Jorgensen
Midlertidigt uden ae oe og aa. Jeg beklager.

---

Nis Jorgensen <nis@superlativ.dk> wrote:

> Hvad hvis du omformulerer til
>
> select * from afles a1 NATURAL JOIN
> (select max(dato) as dato, sted from afles a2
> where dato <= '2005-06-01'
> ) as md
> where sted = 'VM200';

Jeps. Det var netop sådan en anden måde at fomulere mit problem, som jeg
søgte.

Der mangler en 'group by sted' inden i parentesen. Nogle databaser kan
selv sætte det ind, men postgreSQL vil åbenbart have det eksplicit.

> Du kan evt putte sted = 'VM200' indenfor parentesen.

Ja, det virker også med nøjagtigt den samme udførelsestid.

Jeg har overvejet andre muligheder, og egentligt kommet frem til en
endnu simplere:

select * from afles
where sted = 'VM200' and dato <= '2005-06-01'
order by dato desc
limit 1;

Opsummering af udførelsestider med PostgreSQL 8.0.0 under Linux 2.4 på
en Pentium II 400 MHz:

Mit oprindelige query: 429.934 ms
Nis' formulering: 4.975 ms
Mit oprindelige med 'sted' angivet to gange: 3.776 ms
Det sidste med 'limit' og uden sub-query: 0.383 ms

Det koster altså meget tid at bruge et sub-query...

(Jeg begynder helt at ku' li' SQL.)

--
Thorkil Olesen,
Hanstholm.

---

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

slet.dette.thorkil.og.dette@pip.dknet.dk (Thorkil Olesen) writes:

> Nis Jorgensen <nis@superlativ.dk> wrote:
>
>> Hvad hvis du omformulerer til
>>
>> select * from afles a1 NATURAL JOIN
>> (select max(dato) as dato, sted from afles a2
>> where dato <= '2005-06-01'
>> ) as md
>> where sted = 'VM200';
[...]
> Jeg har overvejet andre muligheder, og egentligt kommet frem til en
> endnu simplere:
>
> select * from afles
> where sted = 'VM200' and dato <= '2005-06-01'
> order by dato desc
> limit 1;

Øh... var det bare _det_, du prøvede på? Det var det der med
træthed...

> Opsummering af udførelsestider med PostgreSQL 8.0.0 under Linux 2.4 på
> en Pentium II 400 MHz:
>
> Mit oprindelige query: 429.934 ms
> Nis' formulering: 4.975 ms
> Mit oprindelige med 'sted' angivet to gange: 3.776 ms
> Det sidste med 'limit' og uden sub-query: 0.383 ms
>
> Det koster altså meget tid at bruge et sub-query...

Subqueries ender ofte som joins på den ene eller anden måde, og i
forhold til selects på enkelte tabeller, er joins temmeligt
dyre. Joins har det også med at stige ret voldsomt i køretid, når man
joiner flere større tabeller uden at kunne begrænse søgemængden
effektivt vha. indekser.

Når du laver tidsmålinger som disse, er det også en god idé, at du
forsøger at forstå, hvad databasen faktisk gør for at udføre din
forespørgsel. Dette er blot så du med tiden kan oparbejde en intuition
om, hvordan den slags virker. Dette kan du få rigtigt god hjælp til
med kommandoen EXPLAIN, som giver dig en såkaldt query plan, hvilket
er databasens 'opskrift' på, hvordan din forespørgsel bliver besvaret.

> (Jeg begynder helt at ku' li' SQL.)

Ja, det er sgu helt sjovt at lege med databaser. Jeg er blevet så
smittet, at jeg ikke sjældent dagdrømmer om databasedesigns. Men
nu er jeg også så alvorligt ramt, at jeg har skrevet speciale inden
for området, og mit arbejde er meget databaseorienteret. Hvis ikke du
passer på, kan du ende lige så galt som mig.

Martin

- --
Homepage: http://www.cs.auc.dk/~factotum/
GPG public key: http://www.cs.auc.dk/~factotum/gpgkey.txt
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using Mailcrypt+GnuPG <http://www.gnupg.org>

iEYEARECAAYFAkKg1cYACgkQYu1fMmOQldX7AACginU6SbxmsHv6yxi7VA/kZZuq
3fcAoLFhahEgVq1RPRg1PqvNKkdniHVq
=RmuY
-----END PGP SIGNATURE-----

---

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

slet.dette.thorkil.og.dette@pip.dknet.dk (Thorkil Olesen) writes:

> Forskellen er tilsyneladende, at det ene subquery kører som en
> 'sub', mens det andet kører som en 'init'. Men ellers bliver jeg
> ikke så meget klogere...

Så vidt jeg kan regne ud, er forskellen, at i den første forespørgsel,
bliver subqueryet beregnet for hver række i tabellen i 'den store'
forespørgsel, mens det i anden forspørgsel kun bliver beregnet én
gang.

Som regel bliver subqueries beregnet før resten af de udtryk, de står
i. Når du så har et subquery, der som i første eksempel afhænger af en
variabel fra den ydre forespørgsel, kan subqueryet ikke blive beregnet
én gang for alle, og så genbruges for hver række, der afprøves udenfor
subqueryet. Query planneren er åbenbart ikke smart nok til at
gennemskue, at denne variabel, pga. begrænsningerne i din
WHERE-klausul, faktisk er konstant. I dit andet eksempel bliver
subqueryet derimod kun beregnet den ene gang, det er nødvendigt.

Giver det mening? Jeg er lidt for træt til at stole på mine
pædagogiske evner lige nu.

Martin

- --
Homepage: http://www.cs.auc.dk/~factotum/
GPG public key: http://www.cs.auc.dk/~factotum/gpgkey.txt
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using Mailcrypt+GnuPG <http://www.gnupg.org>

iEYEARECAAYFAkKeLysACgkQYu1fMmOQldWQsgCcCb9bNE48bbZiazipR6dbTzKi
pkMAoJQC5XYZ84qhN2vVExf4Ucfgha16
=ou14
-----END PGP SIGNATURE-----

---

Martin Christensen <martin.sand.christensen@gmail.com> wrote:

> Så vidt jeg kan regne ud, er forskellen, at i den første forespørgsel,
> bliver subqueryet beregnet for hver række i tabellen i 'den store'
> forespørgsel, mens det i anden forspørgsel kun bliver beregnet én
> gang.
>
> Som regel bliver subqueries beregnet før resten af de udtryk, de står
> i. Når du så har et subquery, der som i første eksempel afhænger af en
> variabel fra den ydre forespørgsel, kan subqueryet ikke blive beregnet
> én gang for alle, og så genbruges for hver række, der afprøves udenfor
> subqueryet. Query planneren er åbenbart ikke smart nok til at
> gennemskue, at denne variabel, pga. begrænsningerne i din
> WHERE-klausul, faktisk er konstant. I dit andet eksempel bliver
> subqueryet derimod kun beregnet den ene gang, det er nødvendigt.
>
> Giver det mening? Jeg er lidt for træt til at stole på mine
> pædagogiske evner lige nu.

Ja, det giver god mening. Jeg synes, din forklaring var meget
pædagogisk. Tak for det!

--
Thorkil Olesen,
Hanstholm.