[pdal] PDAL Oracle small benchmark

[Oscar Martinez Rubi]

Hi,

After the latest fixs (thanks Howard, Connor, Andrew and the rest of 
PDAL guys!) in OCI writer and reader and the fact that I found out about 
laz-perf I have done this new small test with PDAL and Oracle to see how 
the two systems behave with different configurations. I tried 
with/without laz-perf, with point/dimension major, with all columns or 
only xyz, with/without BLOB compression and with/without offsets and 
scales (i.e. 64 or 32 bit per coord).

There are 32 combinations, but since lazperf requires dimension major, 
there 24 valid "only" combinations. For each one I have loaded a single 
LAS file with 20M points (size 380 MB, 38MB in LAZ) with the PDAL OCI 
writer and I have queried a small rectangle (80000 points) with PDAL OCI 
reader. I have done the query twice.

 From the attached table is clear that using lazperf is very good in 
size terms (almost factor 2 compared to LAZ), loading time and query 
time!. The best approach is to use lazperf, scales and offsets and 
without BLOB compression.

Regarding the loading times:
  - Adding BLOB compression generally makes the loading slower.
  - Using all columns instead of only xyz also makes it slower.
  - Adding offsets makes it faster.
  - Using lazperf does not seem to have a visible effect in loading time.
  - The point/dimension major doe snot have any visible effect

Regarding the queries the only difference seems to be if using all 
columns or only xyz. In general by having only xyz instead of all 
columns the queries are faster.

Regarding the size/storage the are some strange issues in the numbers of 
the table:

  - When lazperf is used and offsets are used it does not matter in 
storage terms whether I specify BLOB compression or not (I guess that 
BLOB compression just can not squeeze the data anymore, right? or maybe 
it is somehow ignored?)

  - When lazperf is used and offsets are not used the BLOB compression 
actually increases the size (Seems like the BLOB compression messes up 
what lazperf did, strange though...)

  - When lazperf is used it does not matter in storage terms whether I 
specify only x,y,z or all columns. Why is this?

  - When lazperf is not used, the difference in size between point and 
dimension orientation is only visible when using all the columns, BLOB 
compression and offsets

  - When lazperf is not used and only xyz are used the BLOB compression 
offsers same compression factor that using offsets but in twice the 
time, both 278MB. Combining both gives 212MB.

  - When lazperf is not used and all columns are used the BLOB 
compression gives better compression factor that using offsets (343MB vs 
538MB).

  - If BLOB compression is used and offset are not used the lazperf 
compression adds nothing. In fact in this case and having only xyz using 
lazperf is actually worse than not using it (which makes me thing that 
in lazperf all the columns are stored even if you do not want it)

I am aware that the estimation of the size in oracle can be tricky. I 
sum the size of the user segments related to my "blocks" table, i.e.:

SELECT sum(bytes/1024/1024) size_in_MB
FROM user_segments
WHERE (segment_name LIKE : 'blocks%'
    OR segment_name in (
       SELECT segment_name
       FROM user_lobs
       WHERE table_name LIKE : 'blocks%'
       UNION
       SELECT index_name
       FROM user_lobs
       WHERE table_name LIKE : 'blocks%'
       )
);

Kind Regards,

O.
-------------- next part --------------
lazperf dimOri  Cols    BlobCom Offsets LTime   Size    QTime1  Qtime2
True	False	xyz	False	True	30.45	82	0.4	0.37
True	False	all	False	True	41.47	82	0.74	0.67

True	False	xyz	True	True	37.05	82	0.84	0.38
True	False	all	True	True	41.16	82	0.7	0.66

True	False	xyz	False	False	34.45	277	0.44	0.41
True	False	all	False	False	46.26	277	0.72	0.69

True	False	xyz	True	False	45.83	343	0.45	0.41
True	False	all	True	False	50.88	343	1.76	0.72


False	True	xyz	True	True	46.09	212	0.39	0.36
False	False	xyz	True	True	48.36	212	0.38	0.36

False	True	xyz	False	True	28.53	278	0.38	0.42
False	False	xyz	False	True	27.24	278	0.37	0.34

False	True	xyz	True	False	57.39	278	0.39	0.35
False	False	xyz	True	False	76.13	278	0.38	0.42

False	True	all	True	True	57.24	306	0.63	1.44
False	False	all	True	True	58.61	278	0.62	0.59

False	True	all	True	False	68.81	343	0.63	0.6
False	False	all	True	False	68.49	343	0.68	0.6

False	True	all	False	True	37.45	538	0.71	0.6
False	False	all	False	True	43.09	538	0.62	0.79

False	True	xyz	False	False	37.66	538	0.38	0.35
False	False	xyz	False	False	27.7	538	0.41	0.35

False	True	all	False	False	49.74	733	0.63	0.64
False	False	all	False	False	34.31	733	0.68	1.46