3

In our database we use a custom type to store 3D locations:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

In many examples I see INTERNALLENGTH used as a parameter with the definition of custom types. Also, the documentation tells me that "[t]he default assumption is that it is variable-length", if this parameter is omitted. Is this also true for types like the one above where the size can be calculated (3*4 bytes + 4byte padding)? So should I add 16 as internal length to the definition?

Also, are there other parameters that could improve the performance of handling many (~10,000,000) entries using this type?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
tomka
  • 937
  • 1
  • 9
  • 16

1 Answers1

1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc.

What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the manual once more.

To optimize disk space and performance I would rather avoid using composite types due to big row overhead. Details depend on the details of your requirements.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Thanks Erwin, this answers my question. What overhead can I expect when I use this location type? Due to padding I would expect 24 byte. I often run queries that find rows matching a certain bounding box (WHERE l.x > 10000 AND l.x < 12000 AND l.y > 40 AND l.y < 120.4 ...). I've got a B-Tree index on each location component. – tomka Jul 30 '14 at 18:52
  • @tomka: I suggest you ask a new question (or two, actually!). Comments are not the place. You can always add a link to this one for context to avoid redundant typing. – Erwin Brandstetter Jul 30 '14 at 18:55