[PostgreSQL] Timestamp veel langzamer dan date en time?
Ik had een table waarin ik een date-veld en een time-veld had. Gezien ik van plan ben om veel te gaan doen met verschillende tijdzones heb ik de table aangepast zodat er een "timestamp with time zone" en een "timestamp without time zone" in staan (ik moet af en toe ook op lokaal niveau rekenen).
Vervolgens heb ik de query die gebruik maakt van die table aangepast. Probleem is nu dat het script er héél veel langer over doet. Waar het eerst zo'n 500 ms duurde, duurt het nu bijna 40 secondes. Volgens mij heb ik de Primary Key goed staan, maar als ik een EXPLAIN SELECT doe dan krijg ik allemaal Seq. scans, terwijl het met het oude script en de oude table allemaal Index Scans waren. Ik begrijp echter helemaal niet waarom. Is de combinatie date+time zoveel sneller dan timestamp? Of maak ik gewoon een kapitale fout?
Ik zal mijn oude en nieuwe query hieronder plakken, inclusief de EXPLAIN en de table zelf. Misschien dat iemand anders ziet wat ik hier mis doe?
Alle tips zijn welkom!
Oude table:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE minutes_demo_no_id
(
datum date NOT NULL,
tijd time without time zone NOT NULL,
open numeric,
hoog numeric,
laag numeric,
slot numeric,
volume integer,
CONSTRAINT "PK4" PRIMARY KEY (datum, tijd)
)
WITH (
OIDS=FALSE
);
ALTER TABLE minutes_demo_no_id OWNER TO root;
(
datum date NOT NULL,
tijd time without time zone NOT NULL,
open numeric,
hoog numeric,
laag numeric,
slot numeric,
volume integer,
CONSTRAINT "PK4" PRIMARY KEY (datum, tijd)
)
WITH (
OIDS=FALSE
);
ALTER TABLE minutes_demo_no_id OWNER TO root;
oude query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT n1.datum as datum,
n1.tijd as tijd,
1 as tijdspanne,
(n2.open - n1.open) as OvO_verschil,
((SELECT MAX(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd) - n1.open) as hoog,
((SELECT MIN(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd) - n1.open) as laag,
((n2.open - n1.open)/n1.open*100) as OvO_verschil_percentage, ROUND(( ( (SELECT MAX(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd ) - n1.open )/n1.open*100 ), 2) as hoog_percentage,
ROUND(( ( (SELECT MIN(laag) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd ) - n1.open )/n1.open*100 ), 2) as laag_percentage
FROM minutes_demo_no_id n1
JOIN minutes_demo_no_id n2
ON n2.tijd = (n1.tijd + interval '1 minutes' + interval '0 hours') WHERE n1.datum = n2.datum
n1.tijd as tijd,
1 as tijdspanne,
(n2.open - n1.open) as OvO_verschil,
((SELECT MAX(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd) - n1.open) as hoog,
((SELECT MIN(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd) - n1.open) as laag,
((n2.open - n1.open)/n1.open*100) as OvO_verschil_percentage, ROUND(( ( (SELECT MAX(hoog) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd ) - n1.open )/n1.open*100 ), 2) as hoog_percentage,
ROUND(( ( (SELECT MIN(laag) FROM minutes_demo_no_id n3 WHERE n3.datum = n1.datum AND n3.tijd >= n1.tijd AND n3.tijd < n2.tijd ) - n1.open )/n1.open*100 ), 2) as laag_percentage
FROM minutes_demo_no_id n1
JOIN minutes_demo_no_id n2
ON n2.tijd = (n1.tijd + interval '1 minutes' + interval '0 hours') WHERE n1.datum = n2.datum
oude EXPLAIN:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
"Hash Join (cost=131.12..146936.81 rows=3637 width=34)"
" Hash Cond: ((((n1.tijd + '00:01:00'::interval) + '00:00:00'::interval) = n2.tijd) AND (n1.datum = n2.datum))"
" -> Seq Scan on minutes_demo_no_id n1 (cost=0.00..75.25 rows=3725 width=19)"
" -> Hash (cost=75.25..75.25 rows=3725 width=19)"
" -> Seq Scan on minutes_demo_no_id n2 (cost=0.00..75.25 rows=3725 width=19)"
" SubPlan 1"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 2"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 3"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 4"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" Hash Cond: ((((n1.tijd + '00:01:00'::interval) + '00:00:00'::interval) = n2.tijd) AND (n1.datum = n2.datum))"
" -> Seq Scan on minutes_demo_no_id n1 (cost=0.00..75.25 rows=3725 width=19)"
" -> Hash (cost=75.25..75.25 rows=3725 width=19)"
" -> Seq Scan on minutes_demo_no_id n2 (cost=0.00..75.25 rows=3725 width=19)"
" SubPlan 1"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 2"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 3"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
" SubPlan 4"
" -> Aggregate (cost=10.05..10.06 rows=1 width=7)"
" -> Index Scan using "PK4" on minutes_demo_no_id n3 (cost=0.00..10.05 rows=2 width=7)"
" Index Cond: ((datum = $0) AND (tijd >= $1) AND (tijd < $2))"
Table met nieuwe timestamp velden:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE minutes_demo_no_id_tz
(
tijdstempel_tz timestamp with time zone NOT NULL,
tijdstempel_lokaal timestamp without time zone NOT NULL,
open numeric,
hoog numeric,
laag numeric,
slot numeric,
volume integer,
CONSTRAINT "PK8" PRIMARY KEY (tijdstempel_lokaal),
)
WITH (
OIDS=FALSE
);
ALTER TABLE minutes_demo_no_id_tz OWNER TO root;
(
tijdstempel_tz timestamp with time zone NOT NULL,
tijdstempel_lokaal timestamp without time zone NOT NULL,
open numeric,
hoog numeric,
laag numeric,
slot numeric,
volume integer,
CONSTRAINT "PK8" PRIMARY KEY (tijdstempel_lokaal),
)
WITH (
OIDS=FALSE
);
ALTER TABLE minutes_demo_no_id_tz OWNER TO root;
Nieuwe Query met timestamps:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT n1.tijdstempel_tz as tijdstempel_tz,
n1.tijdstempel_lokaal as tijdstempel_lokaal,
1 as tijdspanne,
(n2.open - n1.open) as OvO_verschil,
((SELECT MAX(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open) as hoog,
((SELECT MIN(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open) as laag,
((n2.open - n1.open)/n1.open*100) as OvO_verschil_percentage, ROUND(( ( (SELECT MAX(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open )/n1.open*100 ), 2) as hoog_percentage,
ROUND(( ( (SELECT MIN(laag) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open )/n1.open*100 ), 2) as laag_percentage
FROM minutes_demo_no_id_tz n1
JOIN minutes_demo_no_id_tz n2
ON "time"(n2.tijdstempel_lokaal) = ("time"(n1.tijdstempel_lokaal) + interval '1 minutes' + interval '0 hours') WHERE date(n1.tijdstempel_lokaal) = date(n2.tijdstempel_lokaal)
n1.tijdstempel_lokaal as tijdstempel_lokaal,
1 as tijdspanne,
(n2.open - n1.open) as OvO_verschil,
((SELECT MAX(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open) as hoog,
((SELECT MIN(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open) as laag,
((n2.open - n1.open)/n1.open*100) as OvO_verschil_percentage, ROUND(( ( (SELECT MAX(hoog) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open )/n1.open*100 ), 2) as hoog_percentage,
ROUND(( ( (SELECT MIN(laag) FROM minutes_demo_no_id_tz n3 WHERE date(n3.tijdstempel_lokaal) = date(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) >= "time"(n1.tijdstempel_lokaal) AND "time"(n3.tijdstempel_lokaal) < "time"(n2.tijdstempel_lokaal) ) - n1.open )/n1.open*100 ), 2) as laag_percentage
FROM minutes_demo_no_id_tz n1
JOIN minutes_demo_no_id_tz n2
ON "time"(n2.tijdstempel_lokaal) = ("time"(n1.tijdstempel_lokaal) + interval '1 minutes' + interval '0 hours') WHERE date(n1.tijdstempel_lokaal) = date(n2.tijdstempel_lokaal)
Nieuwe EXPLAIN met de timestamps:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
"Merge Join (cost=592.40..221575.21 rows=347 width=38)"
" Merge Cond: ((((("time"(n1.tijdstempel_lokaal) + '00:01:00'::interval) + '00:00:00'::interval)) = ("time"(n2.tijdstempel_lokaal))) AND ((date(n1.tijdstempel_lokaal)) = (date(n2.tijdstempel_lokaal))))"
" -> Sort (cost=296.20..305.51 rows=3725 width=23)"
" Sort Key: ((("time"(n1.tijdstempel_lokaal) + '00:01:00'::interval) + '00:00:00'::interval)), (date(n1.tijdstempel_lokaal))"
" -> Seq Scan on minutes_demo_no_id_tz n1 (cost=0.00..75.25 rows=3725 width=23)"
" -> Sort (cost=296.20..305.51 rows=3725 width=15)"
" Sort Key: ("time"(n2.tijdstempel_lokaal)), (date(n2.tijdstempel_lokaal))"
" -> Seq Scan on minutes_demo_no_id_tz n2 (cost=0.00..75.25 rows=3725 width=15)"
" SubPlan 1"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 2"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 3"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 4"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" Merge Cond: ((((("time"(n1.tijdstempel_lokaal) + '00:01:00'::interval) + '00:00:00'::interval)) = ("time"(n2.tijdstempel_lokaal))) AND ((date(n1.tijdstempel_lokaal)) = (date(n2.tijdstempel_lokaal))))"
" -> Sort (cost=296.20..305.51 rows=3725 width=23)"
" Sort Key: ((("time"(n1.tijdstempel_lokaal) + '00:01:00'::interval) + '00:00:00'::interval)), (date(n1.tijdstempel_lokaal))"
" -> Seq Scan on minutes_demo_no_id_tz n1 (cost=0.00..75.25 rows=3725 width=23)"
" -> Sort (cost=296.20..305.51 rows=3725 width=15)"
" Sort Key: ("time"(n2.tijdstempel_lokaal)), (date(n2.tijdstempel_lokaal))"
" -> Seq Scan on minutes_demo_no_id_tz n2 (cost=0.00..75.25 rows=3725 width=15)"
" SubPlan 1"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 2"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 3"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
" SubPlan 4"
" -> Aggregate (cost=159.07..159.08 rows=1 width=7)"
" -> Seq Scan on minutes_demo_no_id_tz n3 (cost=0.00..159.06 rows=1 width=7)"
" Filter: (("time"(tijdstempel_lokaal) >= "time"($0)) AND ("time"(tijdstempel_lokaal) < "time"($1)) AND (date(tijdstempel_lokaal) = date($0)))"
Gewijzigd op 15/11/2011 12:06:50 door Peter Sanders
Zorg wel voor bruikbare indexen, anders wordt het niks.
@Bart: waarom een draak van een query? Ongenuanceerd advies om te herschrijven?
Aad B op 16/11/2011 23:07:18:
@Bart: waarom een draak van een query? Ongenuanceerd advies om te herschrijven?
Nee, niet ongenuanceerd, zie de diverse subqueries op dezelfde tabel. Dan weet je vrijwel zeker dat je dit kunt combineren en dat je met één subquery afkunt. Wellicht kun je het ook terugbrengen tot een JOIN, ben je helemaal van de subquery af.
Al kan PostgreSQL zelf ook subqueries omzetten naar een JOIN, maar dat zie je hier niet gebeuren: Zie de subplannen. EXPLAIN ANALYZE kun je gebruiken om de daadwerkelijke verschillen aan te tonen. De configuratie van jouw database heeft hier ook invloed op, daar moet je dan ook even naar kijken. (voor zover dat zin heeft, je hebt niet altijd de rechten om iets aan te passen, denk aan shared hosting)