Thread: performance issue in the fields.

performance issue in the fields.

From
dba
Date:

create table a( address1 int,address2 int,address3 int)
create table b(address int[3])

I have created two tables. In the first table i am using many fields to
store 3 address.
as well as in b table, i am using array data type to store 3 address.  is
there any issue would face in performance related things.... which one will
cause the performance issue.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/performance-issue-in-the-fields-tp3384307p3384307.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: performance issue in the fields.

From
Pavel Stehule
Date:
Hello

2011/2/14 dba <dbadbb@gmail.com>:
>
>
> create table a( address1 int,address2 int,address3 int)
> create table b(address int[3])
>
> I have created two tables. In the first table i am using many fields to
> store 3 address.
> as well as in b table, i am using array data type to store 3 address.  is
> there any issue would face in performance related things.... which one will
> cause the performance issue.

yes, there is. Planner can not to work well with foreign keys stored in array.

Regards

Pavel Stehule

> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/performance-issue-in-the-fields-tp3384307p3384307.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: performance issue in the fields.

From
Merlin Moncure
Date:
On Mon, Feb 14, 2011 at 5:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> 2011/2/14 dba <dbadbb@gmail.com>:
>>
>>
>> create table a( address1 int,address2 int,address3 int)
>> create table b(address int[3])
>>
>> I have created two tables. In the first table i am using many fields to
>> store 3 address.
>> as well as in b table, i am using array data type to store 3 address.  is
>> there any issue would face in performance related things.... which one will
>> cause the performance issue.
>
> yes, there is. Planner can not to work well with foreign keys stored in array.

also the array variant is going to be bigger on disk. This is because
as fields, all the important info about the fields is stored in the
table header (inside the system catalogs).  But with the array,
various header information specific to the array has to be stored with
each row.  This is largely due to some questionable design decisions
made in early array implementation that we are stuck with :-).

merlin

Re: performance issue in the fields.

From
"Pierre C"
Date:
>>> I have created two tables. In the first table i am using many fields to
>>> store 3 address.
>>> as well as in b table, i am using array data type to store 3 address.
>>>  is
>>> there any issue would face in performance related things.... which one
>>> will
>>> cause the performance issue.

The array is interesting :
- if you put a gist index on it and do searches like "array contains
values X and Y and Z", gist index has a some special optimizations for this
- if you might store a variable number of integers, and for some reason
you don't want a normalized one-line-per-value approach