Comments (7)
acbecker
commented on July 30, 2024
@dhalperi Here is a query that builds off of our previous work intersecting trajectories with runs and then images. It uses the UDF we put together earlier this quarter.
WITH TI AS (
WITH TR AS (
SELECT
traj.trajId as trajId,
traj.ra0 as ra0,
traj.dec0 as dec0,
traj.t0 as t0,
traj.delta_ra as delta_ra,
traj.delta_dec as delta_dec,
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmin)),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmax))) as tline
FROM
Trajectory as traj,
Run as run
WHERE
run.bbox &&
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec))
AND
ABS(EXTRACT(EPOCH FROM run.tmax-traj.t0)) < 2592000
)
SELECT
im.imageId, TR.trajId,
(TR.ra0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_ra) as raInt,
(TR.dec0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_dec) as decInt
FROM
Image as im,
TR
WHERE
im.bbox3d &&& TR.tline
)
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt,
c_skyToIdx(w, TI.raInt, TI.decInt)
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId
;
Will check timings next.
from kbmod.
dhalperi
commented on July 30, 2024
I'm pretty sure ingesting pixels will never be worth it. So if we go the
udf route we want the udf to take in ra, dec, and the image and give us the
confidence directly.
Daniel Halperin
Director of Research for Scalable Data Analytics
eScience Institute
University of Washington
On Tue, Dec 9, 2014 at 3:06 PM, Andy Becker [email protected]
wrote:
@dhalperi https://github.com/dhalperi Here is a query that builds off
of our previous work intersecting trajectories with runs and then images.
It uses the UDF we put together earlier this quarter.WITH TI AS (
WITH TR AS (
SELECT
traj.trajId as trajId,
traj.ra0 as ra0,
traj.dec0 as dec0,
traj.t0 as t0,
traj.delta_ra as delta_ra,
traj.delta_dec as delta_dec,
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmin)),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmax))) as tline
FROM
Trajectory as traj,
Run as run
WHERE
run.bbox &&
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec))
AND
ABS(EXTRACT(EPOCH FROM run.tmax-traj.t0)) < 2592000
)
SELECT
im.imageId, TR.trajId,
(TR.ra0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_ra) as raInt,
(TR.dec0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_dec) as decInt
FROM
Image as im,
TR
WHERE
im.bbox3d &&& TR.tline
)
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt,
c_skyToIdx(w, TI.raInt, TI.decInt)
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId
;Will check timings next.
—
Reply to this email directly or view it on GitHub
#4 (comment).
from kbmod.
acbecker
commented on July 30, 2024
Getting strange results on these timings. Here are a couple versions to see where the time is being taken up:
-
Column 1: Full query
... SELECT TI.imageId, TI.trajId, TI.raInt, TI.decInt, c_skyToIdx(w, TI.raInt, TI.decInt) FROM Wcs as w, TI WHERE w.imageId = TI.imageId -
Column 2: Just a join with the Wcs table
... SELECT TI.imageId, TI.trajId, TI.raInt, TI.decInt, w.wcsId FROM Wcs as w, TI WHERE w.imageId = TI.imageId -
Column 3: Just making the 2 temporary tables instead of 1 previously
... SELECT TI.imageId, TI.trajId, TI.raInt, TI.decInt FROM TI
1 8.455 +/- 1.179 1.567 +/- 0.067 0.053 +/- 0.033
5 11.691 +/- 1.274 1.638 +/- 0.049 0.155 +/- 0.075
10 12.752 +/- 0.400 1.666 +/- 0.026 0.188 +/- 0.060
50 12.263 +/- 0.295 1.813 +/- 0.080 0.293 +/- 0.043
100 12.771 +/- 0.642 1.966 +/- 0.045 0.473 +/- 0.056
500 21.576 +/- 1.868 3.664 +/- 0.144 2.113 +/- 0.076
from kbmod.
dhalperi
commented on July 30, 2024
can you post the explains?
Daniel Halperin
Director of Research for Scalable Data Analytics
eScience Institute
University of Washington
On Wed, Dec 10, 2014 at 10:28 AM, Andy Becker [email protected]
wrote:
Getting strange results on these timings. Here are a couple versions to
see where the time is being taken up:
Column 1: Full query
...
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt,
c_skyToIdx(w, TI.raInt, TI.decInt)
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId-Column 2: Just a join with the Wcs table
...
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt, w.wcsId
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId-Column 3: Just making the 2 temporary tables instead of 1 previously
...
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt
FROM
TI1 8.455 +/- 1.179 1.567 +/- 0.067 0.053 +/- 0.033
5 11.691 +/- 1.274 1.638 +/- 0.049 0.155 +/- 0.075
10 12.752 +/- 0.400 1.666 +/- 0.026 0.188 +/- 0.060
50 12.263 +/- 0.295 1.813 +/- 0.080 0.293 +/- 0.043
100 12.771 +/- 0.642 1.966 +/- 0.045 0.473 +/- 0.056
500 21.576 +/- 1.868 3.664 +/- 0.144 2.113 +/- 0.076—
Reply to this email directly or view it on GitHub
#4 (comment).
from kbmod.
acbecker
commented on July 30, 2024
Q1:
EXPLAIN ANALYZE
WITH TI AS (
WITH TR AS (
SELECT
traj.trajId as trajId,
traj.ra0 as ra0,
traj.dec0 as dec0,
traj.t0 as t0,
traj.delta_ra as delta_ra,
traj.delta_dec as delta_dec,
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmin)),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmax))) as tline
FROM
Trajectory as traj,
Run as run
WHERE
run.bbox &&
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec))
AND
ABS(EXTRACT(EPOCH FROM run.tmax-traj.t0)) < 2592000
)
SELECT
im.imageId, TR.trajId,
(TR.ra0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_ra) as raInt,
(TR.dec0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_dec) as decInt
FROM
Image as im,
TR
WHERE
im.bbox3d &&& TR.tline
)
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt,
c_skyToIdx(w, TI.raInt, TI.decInt)
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=50539.21..402698.30 rows=102724 width=468) (actual time=88.530..11818.871 rows=16893 loops=1)
CTE ti
-> Nested Loop (cost=10835.85..50538.78 rows=102724 width=64) (actual time=20.446..1803.417 rows=16893 loops=1)
CTE tr
-> Nested Loop (cost=0.00..10825.85 rows=50 width=64) (actual time=20.261..392.633 rows=8831 loops=1)
Join Filter: ((abs(date_part('epoch'::text, (run.tmax - traj.t0))) < 2592000::double precision) AND (run.bbox && st_makeline(st_makepoint((traj.ra0 +
(date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_dec))), st_makepoint((traj
.ra0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_dec))))))
Rows Removed by Join Filter: 140169
-> Seq Scan on trajectory traj (cost=0.00..10.00 rows=500 width=48) (actual time=0.009..0.383 rows=500 loops=1)
-> Materialize (cost=0.00..11.47 rows=298 width=136) (actual time=0.000..0.113 rows=298 loops=500)
-> Seq Scan on run (cost=0.00..9.98 rows=298 width=136) (actual time=0.007..0.155 rows=298 loops=1)
-> CTE Scan on tr (cost=0.00..1.00 rows=50 width=80) (actual time=20.263..408.706 rows=8831 loops=1)
-> Bitmap Heap Scan on image im (cost=10.00..751.10 rows=205 width=104) (actual time=0.136..0.145 rows=2 loops=8831)
Recheck Cond: (bbox3d &&& tr.tline)
-> Bitmap Index Scan on image_bbox3d_idx (cost=0.00..9.95 rows=205 width=0) (actual time=0.129..0.129 rows=2 loops=8831)
Index Cond: (bbox3d &&& tr.tline)
-> CTE Scan on ti (cost=0.00..2054.48 rows=102724 width=32) (actual time=20.451..1828.328 rows=16893 loops=1)
-> Index Scan using wcs_imageid_idx on wcs w (cost=0.43..3.40 rows=1 width=444) (actual time=0.258..0.261 rows=1 loops=16893)
Index Cond: (imageid = ti.imageid)
Total runtime: 11826.657 ms
(19 rows)
from kbmod.
acbecker
commented on July 30, 2024
Q2:
EXPLAIN ANALYZE
WITH TI AS (
WITH TR AS (
SELECT
traj.trajId as trajId,
traj.ra0 as ra0,
traj.dec0 as dec0,
traj.t0 as t0,
traj.delta_ra as delta_ra,
traj.delta_dec as delta_dec,
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmin)),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmax))) as tline
FROM
Trajectory as traj,
Run as run
WHERE
run.bbox &&
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec))
AND
ABS(EXTRACT(EPOCH FROM run.tmax-traj.t0)) < 2592000
)
SELECT
im.imageId, TR.trajId,
(TR.ra0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_ra) as raInt,
(TR.dec0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_dec) as decInt
FROM
Image as im,
TR
WHERE
im.bbox3d &&& TR.tline
)
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt, w.wcsId
FROM
Wcs as w,
TI
WHERE
w.imageId = TI.imageId
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=206511.71..210877.48 rows=102724 width=36) (actual time=2961.697..5019.968 rows=16893 loops=1)
Hash Cond: (ti.imageid = w.imageid)
CTE ti
-> Nested Loop (cost=10835.85..50538.78 rows=102724 width=64) (actual time=0.250..1946.971 rows=16893 loops=1)
CTE tr
-> Nested Loop (cost=0.00..10825.85 rows=50 width=64) (actual time=0.138..341.822 rows=8831 loops=1)
Join Filter: ((abs(date_part('epoch'::text, (run.tmax - traj.t0))) < 2592000::double precision) AND (run.bbox && st_makeline(st_makepoint((traj.ra0 +
(date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_dec))), st_makepoint((traj
.ra0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_dec))))))
Rows Removed by Join Filter: 140169
-> Seq Scan on trajectory traj (cost=0.00..10.00 rows=500 width=48) (actual time=0.006..0.282 rows=500 loops=1)
-> Materialize (cost=0.00..11.47 rows=298 width=136) (actual time=0.000..0.104 rows=298 loops=500)
-> Seq Scan on run (cost=0.00..9.98 rows=298 width=136) (actual time=0.004..0.110 rows=298 loops=1)
-> CTE Scan on tr (cost=0.00..1.00 rows=50 width=80) (actual time=0.139..379.809 rows=8831 loops=1)
-> Bitmap Heap Scan on image im (cost=10.00..751.10 rows=205 width=104) (actual time=0.166..0.168 rows=2 loops=8831)
Recheck Cond: (bbox3d &&& tr.tline)
-> Bitmap Index Scan on image_bbox3d_idx (cost=0.00..9.95 rows=205 width=0) (actual time=0.160..0.160 rows=2 loops=8831)
Index Cond: (bbox3d &&& tr.tline)
-> CTE Scan on ti (cost=0.00..2054.48 rows=102724 width=32) (actual time=0.252..1993.615 rows=16893 loops=1)
-> Hash (cost=130995.19..130995.19 rows=1998219 width=12) (actual time=2960.485..2960.485 rows=1998219 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 93667kB
-> Seq Scan on wcs w (cost=0.00..130995.19 rows=1998219 width=12) (actual time=0.084..1546.014 rows=1998219 loops=1)
Total runtime: 5063.351 ms
(21 rows)
from kbmod.
acbecker
commented on July 30, 2024
Q3:
EXPLAIN ANALYZE
WITH TI AS (
WITH TR AS (
SELECT
traj.trajId as trajId,
traj.ra0 as ra0,
traj.dec0 as dec0,
traj.t0 as t0,
traj.delta_ra as delta_ra,
traj.delta_dec as delta_dec,
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmin)),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec,
EXTRACT(EPOCH FROM run.tmax))) as tline
FROM
Trajectory as traj,
Run as run
WHERE
run.bbox &&
ST_MakeLine(ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmin-traj.t0) * traj.delta_dec),
ST_MakePoint(traj.ra0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_ra,
traj.dec0 + EXTRACT(EPOCH FROM run.tmax-traj.t0) * traj.delta_dec))
AND
ABS(EXTRACT(EPOCH FROM run.tmax-traj.t0)) < 2592000
)
SELECT
im.imageId, TR.trajId,
(TR.ra0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_ra) as raInt,
(TR.dec0 + EXTRACT(EPOCH FROM im.tmid-TR.t0) * TR.delta_dec) as decInt
FROM
Image as im,
TR
WHERE
im.bbox3d &&& TR.tline
)
SELECT
TI.imageId, TI.trajId, TI.raInt, TI.decInt
FROM
TI
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on ti (cost=50538.78..52593.26 rows=102724 width=32) (actual time=0.254..2013.158 rows=16893 loops=1)
CTE ti
-> Nested Loop (cost=10835.85..50538.78 rows=102724 width=64) (actual time=0.251..1983.392 rows=16893 loops=1)
CTE tr
-> Nested Loop (cost=0.00..10825.85 rows=50 width=64) (actual time=0.142..372.300 rows=8831 loops=1)
Join Filter: ((abs(date_part('epoch'::text, (run.tmax - traj.t0))) < 2592000::double precision) AND (run.bbox && st_makeline(st_makepoint((traj.ra0 +
(date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmin - traj.t0)) * traj.delta_dec))), st_makepoint((traj
.ra0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_ra)), (traj.dec0 + (date_part('epoch'::text, (run.tmax - traj.t0)) * traj.delta_dec))))))
Rows Removed by Join Filter: 140169
-> Seq Scan on trajectory traj (cost=0.00..10.00 rows=500 width=48) (actual time=0.007..0.563 rows=500 loops=1)
-> Materialize (cost=0.00..11.47 rows=298 width=136) (actual time=0.000..0.098 rows=298 loops=500)
-> Seq Scan on run (cost=0.00..9.98 rows=298 width=136) (actual time=0.004..0.096 rows=298 loops=1)
-> CTE Scan on tr (cost=0.00..1.00 rows=50 width=80) (actual time=0.144..392.608 rows=8831 loops=1)
-> Bitmap Heap Scan on image im (cost=10.00..751.10 rows=205 width=104) (actual time=0.169..0.174 rows=2 loops=8831)
Recheck Cond: (bbox3d &&& tr.tline)
-> Bitmap Index Scan on image_bbox3d_idx (cost=0.00..9.95 rows=205 width=0) (actual time=0.161..0.161 rows=2 loops=8831)
Index Cond: (bbox3d &&& tr.tline)
Total runtime: 2017.684 ms
(16 rows)
from kbmod.
Related Issues (4)
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from kbmod.