Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

-----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-----