pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance issue in the fields.
4+ messages / 4 participants
[nested] [flat]
* performance issue in the fields.
@ 2011-02-14 11:33 dba <dbadbb@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: dba @ 2011-02-14 11:33 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance issue in the fields.
@ 2011-02-14 11:36 Pavel Stehule <pavel.stehule@gmail.com>
parent: dba <dbadbb@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2011-02-14 11:36 UTC (permalink / raw)
To: dba <dbadbb@gmail.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance issue in the fields.
@ 2011-02-23 19:59 Merlin Moncure <mmoncure@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Merlin Moncure @ 2011-02-23 19:59 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: dba <dbadbb@gmail.com>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: performance issue in the fields.
@ 2011-02-24 08:22 Pierre C <lists@peufeu.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pierre C @ 2011-02-24 08:22 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: dba <dbadbb@gmail.com>; pgsql-performance
>>> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-02-24 08:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-14 11:33 performance issue in the fields. dba <dbadbb@gmail.com>
2011-02-14 11:36 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-23 19:59 ` Merlin Moncure <mmoncure@gmail.com>
2011-02-24 08:22 ` Pierre C <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox