pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: Postgresql - performance of using array in big database 2+ messages / 2 participants [nested] [flat]
* Re: Postgresql - performance of using array in big database @ 2012-08-08 06:33 Craig Ringer <ringerc@ringerc.id.au> 0 siblings, 0 replies; 2+ messages in thread From: Craig Ringer @ 2012-08-08 06:33 UTC (permalink / raw) To: roberthanco@o2.pl; +Cc: pgsql-performance On 08/03/2012 05:14 PM, roberthanco@o2.pl wrote: > It is read-only table so every integer column have an index. First tip: Define the table without the indexes. INSERT your data, and only after it is inserted create your indexes. Similarly, if you're making huge changes to the table you should consider dropping the indexes, making the changes, and re-creating the indexes. You might not have to drop the indexes if you aren't changing indexed fields, since HOT might save you, but it depends a lot on the specifics of the table's on-disk layout etc. > The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres. > There are many more tables in this database so RAM do not cover all > database. OK, in that case more info on the disk subsystem is generally helpful. Disk spin speed, type? RAID configuration if any? eg: 4 x 750GB 7200RPM Western Digital Black SATA 3 HDDs in RAID 10 using the Linux 'md' raid driver or 2 x 1.5TB 7200RPM "Enterprise/near-line" SATA3 HDDs in RAID 1 using a Dell PARC xxxx controller with BBU in write-back cache mode. ... though if you're only bulk-inserting the BBU doesn't matter much. > | > | I wonder what option would be better in performance point of view. I would advise you to test on a subset of your data. Try loading the same 50,000 records into different databases, one with each structure. Measure how long the load takes for each design, and how long the queries you need to run take to execute. Repeat the process with 500,000 records and see if one design slows down more than the other design does. Etc. > I need to make a good decision because import of this data will take > me a 20 days. For the sheer size of data you have you might want to think about using pg_bulkload. If you can't or don't want to do that, then at least use COPY to load big batches of your data. -- Craig Ringer ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgresql - performance of using array in big database @ 2012-08-09 00:18 Ondrej Ivanič <ondrej.ivanic@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Ondrej Ivanič @ 2012-08-09 00:18 UTC (permalink / raw) To: roberthanco@o2.pl; +Cc: pgsql-performance Hi, On 3 August 2012 19:14, <roberthanco@o2.pl> wrote: > I want to add to table "Item" a column "a_elements" (array type of big > integers) Every record would have not more than 50-60 elements in this > column. > After that i would create index GIN on this column and typical select should > look like this: > select*from item where......and5<@ a_elements; I would use this. > I have also second, more classical, option. > Do not add column a_elements to table item but create table elements with > two columns: > > id_item > id_element > > This table would have around 200 mln records. > I am able to do partitioning on this tables so number of records would > reduce to 20 mln in table elements and 500 K in table item. I do not understand how you can 'reduce to 20 mln'. Do you mean per partition? > The second option select looks like this: > select item.* > from item > leftjoin elements on(item.id_item=elements.id_item) > where.... > and5= elements.id_element > I wonder what option would be better in performance point of view. Is > postgres able to use many different indexes with index GIN (option 1) in a > single query ? Assuming that you partition your tables using id_item. Postgres is not good with partitions if joins are used. Let's have a query: select .. from item left join elements on (item.id_item=elements.id_item) where id_item = 2 needs to scan all partitions in 'elements' table because planner is not smart enough to push where condition to join clause i.e. rewrite query like this (8.4, haven't checked in 9.x releases): select .. from item left join elements on (item.id_item=elements.id_item and elements.id_item = 2) where id_item = 2 In order to use partitioning effectively all you queries need to have constant expression (id_item = 2) in where/join on columns which are used for partitioning -- Ondrej Ivanic (ondrej.ivanic@gmail.com) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-08-09 00:18 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-08-08 06:33 Re: Postgresql - performance of using array in big database Craig Ringer <ringerc@ringerc.id.au> 2012-08-09 00:18 Re: Postgresql - performance of using array in big database Ondrej Ivanič <ondrej.ivanic@gmail.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