Table "public.pwprijs" Column | Type | Modifiers -----------------+---------+----------- produktid | integer | not null leverancierid | integer | not null prijs | real | not null Time | bigint | not null recordtimestamp | bigint | not null Indexes: "pwprijs_levid_idx" btree (leverancierid), tablespace "raptor" "pwprijs_levid_pid_idx" btree (leverancierid, produktid), tablespace "raptor" "pwprijs_levid_rects_idx" btree (leverancierid, recordtimestamp), tablespace "raptor" "pwprijs_produktid_timestamp_idx" btree (produktid, recordtimestamp), tablespace "raptor" "pwprijs_rec_levid_pid" btree (recordtimestamp, leverancierid, produktid), tablespace "raptor" "pwprijs_recordtimestamp_idx" btree (recordtimestamp), tablespace "raptor" "pwprijs_recordtimestamp_produktid_prijs_idx" btree (recordtimestamp, produktid, prijs), tablespace "raptor" Tablespace: "raptor" Table "public.pwprodukten" Column | Type | Modifiers -----------------+-----------------------+------------------------------ id | integer | not null naam | character varying(80) | not null cat2 | smallint | not null recordtimestamp | bigint | not null Indexes: "pwprodukten_pkey" PRIMARY KEY, btree (id) "pwprodukten_cat_idx" btree (cat2) SELECT CAST('epoch'::timestamp + (chart_2.RecordTimestamp - (chart_2.RecordTimestamp % 86400)) * interval '1 second' as date) as grouper, chart_2.Prijs as field_2_0 FROM pwprijs as chart_2 JOIN pwprodukten t_0 ON chart_2.ProduktID = t_0.ID AND t_0.Cat2 IN (SELECT 545 UNION SELECT ID FROM cat WHERE ParentID = 545) JOIN pwprijs t_1 ON chart_2.ProduktID = t_1.ProduktID AND t_1.LeverancierID = 938 AND t_1.recordtimestamp >= (SELECT max_date - 60 FROM last_dates WHERE table_name = 'pricetracker') WHERE chart_2.Prijs > 0 Hash Join (cost=72062.34..940100.37 rows=22901407 width=12) (actual time=746.520..29655.748 rows=58065 loops=1) Hash Cond: ("outer".produktid = "inner".id) InitPlan -> Seq Scan on last_dates (cost=0.00..1.06 rows=1 width=8) (actual time=0.041..0.045 rows=1 loops=1) Filter: ((table_name)::text = 'pricetracker'::text) -> Seq Scan on pwprijs chart_2 (cost=0.00..156721.59 rows=7691800 width=16) (actual time=8.457..14713.939 rows=7691260 loops=1) Filter: (prijs > 0::double precision) -> Hash (cost=71769.47..71769.47 rows=42319 width=8) (actual time=606.996..606.996 rows=103 loops=1) -> Hash Join (cost=4329.24..71769.47 rows=42319 width=8) (actual time=599.771..606.782 rows=103 loops=1) Hash Cond: ("outer".produktid = "inner".id) -> Bitmap Heap Scan on pwprijs t_1 (cost=787.84..65786.28 rows=84640 width=4) (actual time=36.049..40.618 rows=4405 loops=1) Recheck Cond: ((leverancierid = 938) AND (recordtimestamp >= $1)) -> Bitmap Index Scan on pwprijs_levid_rects_idx (cost=0.00..787.84 rows=84640 width=0) (actual time=36.004..36.004 rows=4405 loops=1) Index Cond: ((leverancierid = 938) AND (recordtimestamp >= $1)) -> Hash (cost=3399.01..3399.01 rows=22156 width=4) (actual time=561.313..561.313 rows=1458 loops=1) -> Seq Scan on pwprodukten t_0 (cost=10.45..3399.01 rows=22156 width=4) (actual time=60.957..559.595 rows=1458 loops=1) Filter: (subplan) SubPlan -> Materialize (cost=10.45..10.53 rows=8 width=2) (actual time=0.002..0.006 rows=8 loops=44313) -> Unique (cost=10.40..10.44 rows=8 width=2) (actual time=44.831..44.846 rows=8 loops=1) -> Sort (cost=10.40..10.42 rows=8 width=2) (actual time=44.829..44.834 rows=8 loops=1) Sort Key: "?column?" -> Append (cost=0.00..10.28 rows=8 width=2) (actual time=0.006..44.754 rows=8 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.004..0.006 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=2.02..10.26 rows=7 width=2) (actual time=44.723..44.738 rows=7 loops=1) -> Bitmap Heap Scan on cat (cost=2.02..10.19 rows=7 width=2) (actual time=44.719..44.726 rows=7 loops=1) Recheck Cond: (parentid = 545) -> Bitmap Index Scan on cat_parentid_idx (cost=0.00..2.02 rows=7 width=0) (actual time=44.704..44.704 rows=7 loops=1) Index Cond: (parentid = 545) Total runtime: 29688.736 ms SELECT CAST('epoch'::timestamp + (chart_2.RecordTimestamp - (chart_2.RecordTimestamp % 86400)) * interval '1 second' as date) as grouper, chart_2.Prijs as field_2_0 FROM pwprijs as chart_2 WHERE chart_2.ProduktID IN (SELECT ID FROM pwprodukten WHERE Cat2 IN (SELECT 545 UNION SELECT ID FROM cat WHERE ParentID = 545)) AND chart_2.ProduktID IN (SELECT produktid FROM pwprijs WHERE LeverancierID = 938 AND recordtimestamp >= (SELECT max_date - 60 FROM last_dates WHERE table_name = 'pricetracker')) AND chart_2.Prijs > 0 Nested Loop IN Join (cost=2850.25..230556.55 rows=1699 width=12) (actual time=482.862..24419.848 rows=58065 loops=1) InitPlan -> Seq Scan on last_dates (cost=0.00..1.06 rows=1 width=8) (actual time=0.022..0.025 rows=1 loops=1) Filter: ((table_name)::text = 'pricetracker'::text) -> Hash IN Join (cost=2849.18..200109.24 rows=207947 width=20) (actual time=360.511..15450.592 rows=190487 loops=1) Hash Cond: ("outer".produktid = "inner".id) -> Seq Scan on pwprijs chart_2 (cost=0.00..156721.59 rows=7691800 width=16) (actual time=11.587..10137.642 rows=7691260 loops=1) Filter: (prijs > 0::double precision) -> Hash (cost=2846.19..2846.19 rows=1198 width=4) (actual time=244.576..244.576 rows=1458 loops=1) -> Nested Loop (cost=12.93..2846.19 rows=1198 width=4) (actual time=48.646..243.239 rows=1458 loops=1) -> Unique (cost=10.40..10.44 rows=8 width=2) (actual time=19.552..19.580 rows=8 loops=1) -> Sort (cost=10.40..10.42 rows=8 width=2) (actual time=19.550..19.559 rows=8 loops=1) Sort Key: "?column?" -> Append (cost=0.00..10.28 rows=8 width=2) (actual time=0.005..19.532 rows=8 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.005..0.007 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=2.02..10.26 rows=7 width=2) (actual time=19.501..19.518 rows=7 loops=1) -> Bitmap Heap Scan on cat (cost=2.02..10.19 rows=7 width=2) (actual time=19.499..19.506 rows=7 loops=1) Recheck Cond: (parentid = 545) -> Bitmap Index Scan on cat_parentid_idx (cost=0.00..2.02 rows=7 width=0) (actual time=19.493..19.493 rows=7 loops=1) Index Cond: (parentid = 545) -> Bitmap Heap Scan on pwprodukten (cost=2.52..352.58 rows=150 width=6) (actual time=3.717..27.732 rows=182 loops=8) Recheck Cond: (pwprodukten.cat2 = "outer"."?column?") -> Bitmap Index Scan on pwprodukten_cat_idx (cost=0.00..2.52 rows=150 width=0) (actual time=1.510..1.510 rows=182 loops=8) Index Cond: (pwprodukten.cat2 = "outer"."?column?") -> Index Scan using pwprijs_levid_pid_idx on pwprijs (cost=0.00..34.16 rows=3 width=4) (actual time=0.044..0.044 rows=0 loops=190487) Index Cond: ((pwprijs.leverancierid = 938) AND ("outer".produktid = pwprijs.produktid)) Filter: (recordtimestamp >= $0) Total runtime: 24466.560 ms SELECT CAST('epoch'::timestamp + (chart_2.RecordTimestamp - (chart_2.RecordTimestamp % 86400)) * interval '1 second' as date) as grouper, chart_2.Prijs as field_2_0 FROM pwprijs as chart_2 WHERE chart_2.ProduktID IN (SELECT ID FROM pwprodukten WHERE Cat2 IN (SELECT 545 UNION SELECT ID FROM cat WHERE ParentID = 545) INTERSECT SELECT produktid FROM pwprijs WHERE LeverancierID = 938 AND recordtimestamp >= (SELECT max_date - 60 FROM last_dates WHERE table_name = 'pricetracker')) AND chart_2.Prijs > 0 Hash Join (cost=77791.44..457846.83 rows=2010732 width=12) (actual time=409.838..13202.566 rows=58065 loops=1) Hash Cond: ("outer".produktid = "inner".id) -> Seq Scan on pwprijs chart_2 (cost=0.00..156721.59 rows=7691800 width=16) (actual time=7.896..8253.585 rows=7691260 loops=1) Filter: (prijs > 0::double precision) -> Hash (cost=77769.98..77769.98 rows=8584 width=4) (actual time=325.008..325.008 rows=103 loops=1) -> SetOp Intersect (cost=77254.95..77684.14 rows=8584 width=4) (actual time=317.384..324.932 rows=103 loops=1) -> Sort (cost=77254.95..77469.55 rows=85838 width=4) (actual time=317.361..320.519 rows=5863 loops=1) Sort Key: id -> Append (cost=12.93..69491.92 rows=85838 width=4) (actual time=38.765..308.837 rows=5863 loops=1) -> Subquery Scan "*SELECT* 1" (cost=12.93..2858.17 rows=1198 width=4) (actual time=38.763..256.795 rows=1458 loops=1) -> Nested Loop (cost=12.93..2846.19 rows=1198 width=4) (actual time=38.761..254.989 rows=1458 loops=1) -> Unique (cost=10.40..10.44 rows=8 width=2) (actual time=8.994..9.027 rows=8 loops=1) -> Sort (cost=10.40..10.42 rows=8 width=2) (actual time=8.993..9.000 rows=8 loops=1) Sort Key: "?column?" -> Append (cost=0.00..10.28 rows=8 width=2) (actual time=0.003..8.978 rows=8 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.005 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=2.02..10.26 rows=7 width=2) (actual time=8.949..8.963 rows=7 loops=1) -> Bitmap Heap Scan on cat (cost=2.02..10.19 rows=7 width=2) (actual time=8.945..8.952 rows=7 loops=1) Recheck Cond: (parentid = 545) -> Bitmap Index Scan on cat_parentid_idx (cost=0.00..2.02 rows=7 width=0) (actual time=8.940..8.940 rows=7 loops=1) Index Cond: (parentid = 545) -> Bitmap Heap Scan on pwprodukten (cost=2.52..352.58 rows=150 width=6) (actual time=3.847..30.522 rows=182 loops=8) Recheck Cond: (pwprodukten.cat2 = "outer"."?column?") -> Bitmap Index Scan on pwprodukten_cat_idx (cost=0.00..2.52 rows=150 width=0) (actual time=1.583..1.583 rows=182 loops=8) Index Cond: (pwprodukten.cat2 = "outer"."?column?") -> Subquery Scan "*SELECT* 2" (cost=788.90..66633.75 rows=84640 width=4) (actual time=36.653..46.266 rows=4405 loops=1) -> Bitmap Heap Scan on pwprijs (cost=788.90..65787.35 rows=84640 width=4) (actual time=36.651..41.326 rows=4405 loops=1) Recheck Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) InitPlan -> Seq Scan on last_dates (cost=0.00..1.06 rows=1 width=8) (actual time=0.025..0.029 rows=1 loops=1) Filter: ((table_name)::text = 'pricetracker'::text) -> Bitmap Index Scan on pwprijs_levid_rects_idx (cost=0.00..787.84 rows=84640 width=0) (actual time=36.614..36.614 rows=4405 loops=1) Index Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) Total runtime: 13234.570 ms set enable_seqscan = false; without prijs-clause: Nested Loop (cost=77299.32..7858118.21 rows=2013780 width=12) (actual time=33.964..634.359 rows=58065 loops=1) -> SetOp Intersect (cost=77296.51..77727.66 rows=8623 width=4) (actual time=33.632..42.356 rows=103 loops=1) -> Sort (cost=77296.51..77512.08 rows=86231 width=4) (actual time=33.616..36.979 rows=5863 loops=1) Sort Key: id -> Append (cost=12.93..69495.92 rows=86231 width=4) (actual time=0.214..25.380 rows=5863 loops=1) -> Subquery Scan "*SELECT* 1" (cost=12.93..2858.17 rows=1198 width=4) (actual time=0.213..8.779 rows=1458 loops=1) -> Nested Loop (cost=12.93..2846.19 rows=1198 width=4) (actual time=0.211..7.065 rows=1458 loops=1) -> Unique (cost=10.40..10.44 rows=8 width=2) (actual time=0.095..0.122 rows=8 loops=1) -> Sort (cost=10.40..10.42 rows=8 width=2) (actual time=0.093..0.099 rows=8 loops=1) Sort Key: "?column?" -> Append (cost=0.00..10.28 rows=8 width=2) (actual time=0.003..0.079 rows=8 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.003..0.005 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=2.02..10.26 rows=7 width=2) (actual time=0.050..0.065 rows=7 loops=1) -> Bitmap Heap Scan on cat (cost=2.02..10.19 rows=7 width=2) (actual time=0.048..0.054 rows=7 loops=1) Recheck Cond: (parentid = 545) -> Bitmap Index Scan on cat_parentid_idx (cost=0.00..2.02 rows=7 width=0) (actual time=0.040..0.040 rows=7 loops=1) Index Cond: (parentid = 545) -> Bitmap Heap Scan on pwprodukten (cost=2.52..352.58 rows=150 width=6) (actual time=0.076..0.658 rows=182 loops=8) Recheck Cond: (pwprodukten.cat2 = "outer"."?column?") -> Bitmap Index Scan on pwprodukten_cat_idx (cost=0.00..2.52 rows=150 width=0) (actual time=0.056..0.056 rows=182 loops=8) Index Cond: (pwprodukten.cat2 = "outer"."?column?") -> Subquery Scan "*SELECT* 2" (cost=794.53..66637.75 rows=85033 width=4) (actual time=1.064..10.785 rows=4405 loops=1) -> Bitmap Heap Scan on pwprijs (cost=794.53..65787.42 rows=85033 width=4) (actual time=1.062..5.792 rows=4405 loops=1) Recheck Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) InitPlan -> Index Scan using last_dates_pkey on last_dates (cost=0.00..3.33 rows=1 width=8) (actual time=0.042..0.044 rows=1 loops=1) Index Cond: ((table_name)::text = 'pricetracker'::text) -> Bitmap Index Scan on pwprijs_levid_rects_idx (cost=0.00..791.20 rows=85033 width=0) (actual time=1.027..1.027 rows=4405 loops=1) Index Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) -> Bitmap Heap Scan on pwprijs chart_2 (cost=2.82..895.85 rows=234 width=16) (actual time=0.363..3.980 rows=564 loops=103) Recheck Cond: (chart_2.produktid = "outer".id) -> Bitmap Index Scan on pwprijs_produktid_timestamp_idx (cost=0.00..2.82 rows=234 width=0) (actual time=0.206..0.206 rows=564 loops=103) Index Cond: (chart_2.produktid = "outer".id) Total runtime: 665.335 ms with prijs-clause: Hash Join (cost=134379.17..514503.02 rows=2013674 width=12) (actual time=3089.037..15063.409 rows=58065 loops=1) Hash Cond: ("outer".produktid = "inner".id) -> Bitmap Heap Scan on pwprijs chart_2 (cost=56543.72..213260.22 rows=7691800 width=16) (actual time=2972.869..10299.608 rows=7691260 loops=1) Recheck Cond: (prijs > 0::double precision) -> Bitmap Index Scan on pwprijs_recordtimestamp_produktid_prijs_idx (cost=0.00..56543.72 rows=7691800 width=0) (actual time=2962.018..2962.018 rows=7691260 loops=1) Index Cond: (prijs > 0::double precision) -> Hash (cost=77813.89..77813.89 rows=8623 width=4) (actual time=41.373..41.373 rows=103 loops=1) -> SetOp Intersect (cost=77296.51..77727.66 rows=8623 width=4) (actual time=33.770..41.297 rows=103 loops=1) -> Sort (cost=77296.51..77512.08 rows=86231 width=4) (actual time=33.756..36.971 rows=5863 loops=1) Sort Key: id -> Append (cost=12.93..69495.92 rows=86231 width=4) (actual time=0.247..25.379 rows=5863 loops=1) -> Subquery Scan "*SELECT* 1" (cost=12.93..2858.17 rows=1198 width=4) (actual time=0.246..8.816 rows=1458 loops=1) -> Nested Loop (cost=12.93..2846.19 rows=1198 width=4) (actual time=0.243..7.092 rows=1458 loops=1) -> Unique (cost=10.40..10.44 rows=8 width=2) (actual time=0.110..0.135 rows=8 loops=1) -> Sort (cost=10.40..10.42 rows=8 width=2) (actual time=0.108..0.115 rows=8 loops=1) Sort Key: "?column?" -> Append (cost=0.00..10.28 rows=8 width=2) (actual time=0.004..0.092 rows=8 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.004..0.006 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=2.02..10.26 rows=7 width=2) (actual time=0.062..0.077 rows=7 loops=1) -> Bitmap Heap Scan on cat (cost=2.02..10.19 rows=7 width=2) (actual time=0.059..0.066 rows=7 loops=1) Recheck Cond: (parentid = 545) -> Bitmap Index Scan on cat_parentid_idx (cost=0.00..2.02 rows=7 width=0) (actual time=0.042..0.042 rows=7 loops=1) Index Cond: (parentid = 545) -> Bitmap Heap Scan on pwprodukten (cost=2.52..352.58 rows=150 width=6) (actual time=0.078..0.658 rows=182 loops=8) Recheck Cond: (pwprodukten.cat2 = "outer"."?column?") -> Bitmap Index Scan on pwprodukten_cat_idx (cost=0.00..2.52 rows=150 width=0) (actual time=0.058..0.058 rows=182 loops=8) Index Cond: (pwprodukten.cat2 = "outer"."?column?") -> Subquery Scan "*SELECT* 2" (cost=794.53..66637.75 rows=85033 width=4) (actual time=1.075..10.724 rows=4405 loops=1) -> Bitmap Heap Scan on pwprijs (cost=794.53..65787.42 rows=85033 width=4) (actual time=1.072..5.846 rows=4405 loops=1) Recheck Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) InitPlan -> Index Scan using last_dates_pkey on last_dates (cost=0.00..3.33 rows=1 width=8) (actual time=0.045..0.047 rows=1 loops=1) Index Cond: ((table_name)::text = 'pricetracker'::text) -> Bitmap Index Scan on pwprijs_levid_rects_idx (cost=0.00..791.20 rows=85033 width=0) (actual time=1.037..1.037 rows=4405 loops=1) Index Cond: ((leverancierid = 938) AND (recordtimestamp >= $0)) Total runtime: 15095.402 ms