-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Benjamin Arai wrote: > As stated in the previous email if I use partitioning then queries will > be executed sequentially - i.e., instead of log(n) it would be (# > partitions) * log(n). Right? The planner will consider every relevant partition during the execution. Which may be a performance hit, it may not be. It depends on many factors. In general however, partitioning when done correctly is a performance benefit and a maintenance benefit. Sincerely, Joshua D. Drake > > Benjamin > > On Aug 25, 2007, at 9:18 AM, Joshua D. Drake wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 > >> Benjamin Arai wrote: >>> This kind of disappointing, I was hoping there was more that could be >>> done. >>> >>> There has to be another way to do incremental indexing without loosing >>> that much performance. > >> What makes you think you are loosing performance by using partitioning? > >> Joshua D. Drake > >>> >>> Benjamin >>> >>> On Aug 24, 2007, at 6:29 PM, Joshua D. Drake wrote: >>> >>>> -----BEGIN PGP SIGNED MESSAGE----- >>>> Hash: SHA1 >>> >>>> Brandon Shalton wrote: >>>>> Benjamin, >>>>> >>>>> >>>>>> >>>>>> In order to avoid the re-indexing I was thinking of instead creating >>>>>> a new >>>>>> table each month (building its indexes and etc) and accessing them >>>>>> all >>>>>> through a view. This way I only have to index the new data each >>>>>> month. >>>>>> >>>>> >>>>> Take a look at bizgres.org (based on postgres). >>>>> >>>>> They have a parent-child structure. >>>>> >>>>> The way i use it, is I have about 30M records a day that are inserted >>>>> into the database. >>>>> >>>>> Each day is a "child" table to the "parent". >>>>> >>>>> so example: >>>>> >>>>> the parent table is called "logfile" >>>>> >>>>> each day, is a child, with the structure like "logfile_YYMMDD" >>>>> >>>>> the "child" inherits the table structure of the parent, such that you >>>>> could query the child table name directly, or you run the query >>>>> against >>>>> the parent (ie. logfile table) and get all the data. >>>>> >>>>> the indexes are done on a per table basis, so new data that comes >>>>> in, is >>>>> a lesser amount, and doesn't require re-indexing. >>> >>> >>>> PostgreSQL can do all of this too. >>> >>>> Sincerely, >>> >>>> Joshua D. Drake >>> >>>>> >>>>> >>>>> example: >>>>> >>>>> select * from logfile_070825 where datafield = 'foo' >>>>> >>>>> if i knew i wanted to specifically go into that child, or: >>>>> >>>>> select * from logfile where datafield = 'foo' >>>>> >>>>> and all child tables are searched and results merged. You can perform >>>>> any kind of sql query and field structures are you normally do. >>>>> >>>>> the downside is that the queries are run sequentially. >>>>> >>>>> so if you had 100 child tables, each table is queried via indexes, >>>>> then >>>>> results are merged. >>>>> >>>>> but, this approach does allow me to dump alot of data in, without >>>>> having >>>>> the re-indexing issues you are facing. >>>>> >>>>> at some point, you could roll up the days, in to weekly child tables, >>>>> then monthly tables, etc. >>>>> >>>>> I believe Bizgres has a new version of their system that does parallel >>>>> queries which would certainly speed things up. >>>>> >>>>> For your documents, you can do it by the day it was checked in, or >>>>> maybe >>>>> you have some other way of logically grouping, but the parent/child >>>>> table structure really helped to solve my problem of adding in >>>>> millions >>>>> of records each day. >>>>> >>>>> The closest thing in mysql is using merge tables, which is not really >>>>> practical when it comes time to do the joins to the tables. >>>>> >>>>> -brandon >>>>> >>>>> http://www.t3report.com - marketing intelligence for online marketing >>>>> and affiliate programs >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> ---------------------------(end of >>>>> broadcast)--------------------------- >>>>> TIP 6: explain analyze is your friend >>>>> >>> >>> >>>> - -- >>> >>>> === The PostgreSQL Company: Command Prompt, Inc. === >>>> Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 >>>> PostgreSQL solutions since 1997 http://www.commandprompt.com/ >>>> UNIQUE NOT NULL >>>> Donate to the PostgreSQL Project: >>>> http://www.postgresql.org/about/donate >>>> PostgreSQL Replication: http://www.commandprompt.com/products/ >>> >>>> > > >> - ---------------------------(end of >> broadcast)--------------------------- >> TIP 6: explain analyze is your friend > > > >> - -- > >> === The PostgreSQL Company: Command Prompt, Inc. === >> Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 >> PostgreSQL solutions since 1997 http://www.commandprompt.com/ >> UNIQUE NOT NULL >> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate >> PostgreSQL Replication: http://www.commandprompt.com/products/ > >> - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG0HJ5ATb/zqfZUUQRAuEdAJwNwsr/XCsr85tElSVbRVMUHME+PACglbJK gj5cZgOtgEEjUPph0jpsOcw= =u7Ox -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
