Thread: performance issue in the fields.
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.
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 >
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
>>> 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