PHPhulp.nl
35k+ leden Over PHPhulp Offerte opdracht Contact
P

[PostgreSQL] Timestamp veel langzamer dan date en time?

Door Peter Sanders op 15-11-2011 12:04 gewijzigd op 15-11-2011 12:06
1.729 views
Hallo,

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:
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;

oude query:
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

oude EXPLAIN:
"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))"


Table met nieuwe timestamp velden:
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;


Nieuwe Query met timestamps:
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)

Nieuwe EXPLAIN met de timestamps:
"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)))"
B Bartje Jansen
16-11-2011 22:48
Dit is een draak van een query, ga die eerst eens herschrijven. Werk alle subqueries weg, gebruik JOIN's. Hou het simpel en ga het dan langzaam maar zeker uitbreiden, per stap een EXPLAIN ANALYZE gebruiken en je ziet hoe het ervoor staat.

Zorg wel voor bruikbare indexen, anders wordt het niks.
A Aad B
16-11-2011 23:07
500 ms is eigenlijk wel lang maar als dat aanvaardbaar was dan moet je nu op zoek naar de juiste benadering qua indexen, heb je op de nieuwe velden ook weer indexen?
@Bart: waarom een draak van een query? Ongenuanceerd advies om te herschrijven?
B Bartje Jansen
17-11-2011 19:15
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)

Reageren

Inloggen om te reageren