Thread: Tablepartitioning: Will it be supported in Future?

Tablepartitioning: Will it be supported in Future?

From
"apoc9009@yahoo.de"
Date:
Hi PSQL Hackers,

I have done the following Post to PSQL Performance, but Richard Huxton 
say it
might be a better Idea to post it again on you List so i forward the 
message.

apoc9009@yahoo.de wrote:

> Hi all,
>
> Ia a Guy from Germany an a strong Postgres believer!
> It is the best OpenSource Database i have ever  have bee tasted and i 
> try to using
> it in any Database Environments.
>
> It is exiting to see thadt Verison 8.0 has Tablespaces like ORACLE and 
> DB/2,
> but i need Partitioning on a few very large Tables.


I believe these are being worked on at the moment. You might want to 
search the archives of the hackers mailing list to see if the plans will 
suit your needs.

--  Richard Huxton Archonet Ltd



Re: Tablepartitioning: Will it be supported in Future?

From
"Qingqing Zhou"
Date:
<apoc9009@yahoo.de> writes
> I have done the following Post to PSQL Performance, but Richard Huxton
> say it
> might be a better Idea to post it again on you List so i forward the
> message.
...
> It is exiting to see thadt Verison 8.0 has Tablespaces like ORACLE and
> DB/2,
> but i need Partitioning on a few very large Tables.
...

Richard said you can *search* in this list ...  Here are two links might be
of your interests:

http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php  - how
to do horizonal partition using inheritance;

http://archives.postgresql.org/pgsql-performance/2005-03/msg00370.php - a
long list of discussing the future partition implementation;


Regards,
Qingqing




Re: Tablepartitioning: Will it be supported in Future?

From
"apoc9009@yahoo.de"
Date:
Ok!
The Links your posted are great and i guessing it will help me a lot!

But the other Question (if Tablepartitioning under Developemt and will 
it be supported generally)
is still alive.

Josh

Qingqing Zhou wrote:

><apoc9009@yahoo.de> writes
>  
>
>>I have done the following Post to PSQL Performance, but Richard Huxton
>>say it
>>might be a better Idea to post it again on you List so i forward the
>>message.
>>    
>>
>...
>  
>
>>It is exiting to see thadt Verison 8.0 has Tablespaces like ORACLE and
>>DB/2,
>>but i need Partitioning on a few very large Tables.
>>    
>>
>...
>
>Richard said you can *search* in this list ...  Here are two links might be
>of your interests:
>
>http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php  - how
>to do horizonal partition using inheritance;
>
>http://archives.postgresql.org/pgsql-performance/2005-03/msg00370.php - a
>long list of discussing the future partition implementation;
>
>
>Regards,
>Qingqing
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>  
>   *





bitmapscan test, no success, bs is not faster

From
Pavel Stehule
Date:
Hello, 

I tested bitmap scan and maybe I didnt find good examples, but with bitmap 
scan is slower than hashjoin. Only when I use non otiptimized SELECT bps 
was little bit faster. All my SELECTs are equal.
          bsp off         bsp on (ms)
SELECT 1   39.798          37.993
SELECT 2   0.310           0.330
SELECT 3   0.387           0.416

SQL and query plans are in attachment.

Regards
Pavel Stehule

Re: bitmapscan test, no success, again

From
Pavel Stehule
Date:
Hello,

I get success, with my hyphotetic sample on big table (100000 rec) Bitmap 
index scan is really usefull.

I tested select count(*) from foo where  v IN (11,11,23,11,11,11,11,11,22,71,11)

Some notes.

1. with bitmap index scan 7.16 ms 
2. without bis 165.731 ms (seq. scan) !!!!

3. list of constant contains equals values. Is possible remove it? explain analyze select count(*) from foo   where v
in(11,11,11,12)
 

Regards

Pavel Stehule 






Re: bitmapscan test, no success, bs is not faster

From
Tom Lane
Date:
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> I tested bitmap scan and maybe I didnt find good examples, but with bitmap 
> scan is slower than hashjoin. Only when I use non otiptimized SELECT bps 
> was little bit faster. All my SELECTs are equal.

Bitmap scans can't possibly be any faster for cases where the indexscan
only fetches one row, which is true of all your test cases AFAICS.

It should be at least marginally faster than the old code for cases
involving overlapping ORed conditions, that isWHERE some-indexable-condition OR some-other-indexable-condition
where the conditions retrieve some of the same rows.

But I think the real win will come on ANDing of distinct indexes, that
isWHERE condition-for-index-A AND condition-for-index-B
where neither of the index conditions is individually very selective but
together they select just a few rows.  Before, the optimizer could only
choose one index or the other ... but now it can use both.

An example in the regression database is

regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                                     QUERY PLAN
 

------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on tenk1  (cost=19.91..234.07 rows=94 width=244) (actual time=7.372..8.560 rows=100 loops=1)  Recheck Cond:
((hundred>= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))  ->  BitmapAnd  (cost=19.91..19.91
rows=94width=0) (actual time=7.094..7.094 rows=0 loops=1)        ->  Bitmap Index Scan on tenk1_hundred
(cost=0.00..9.62rows=937 width=0) (actual time=3.210..3.210 rows=1000 loops=1)              Index Cond: ((hundred >= 1)
AND(hundred <= 10))        ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..10.04 rows=1007 width=0) (actual
time=3.147..3.147rows=1000 loops=1)              Index Cond: ((thousand >= 1) AND (thousand <= 100))Total runtime:
9.505ms
 
(8 rows)

In 8.0 this looks like

regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                   QUERY PLAN
 
-------------------------------------------------------------------------------------------------------Seq Scan on
tenk1 (cost=0.00..558.00 rows=99 width=244) (actual time=0.171..69.189 rows=100 loops=1)  Filter: ((hundred >= 1) AND
(hundred<= 10) AND (thousand >= 1) AND (thousand <= 100))Total runtime: 70.013 ms
 
(3 rows)

The optimizer is a bit off on the relative merits of seqscan and
indexscan for this case, but even the indexscan is not in the same
ballpark, because it has to choose just one index to use:

regression=# set enable_seqscan to 0;
SET
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                               QUERY PLAN
 

-------------------------------------------------------------------------------------------------------------------------------Index
Scanusing tenk1_hundred on tenk1  (cost=0.00..1455.48 rows=99 width=244) (actual time=10.762..24.454 rows=100 loops=1)
IndexCond: ((hundred >= 1) AND (hundred <= 10))  Filter: ((thousand >= 1) AND (thousand <= 100))Total runtime: 25.384
ms
(4 rows)

        regards, tom lane


Re: bitmapscan test, no success, bs is not faster

From
Oleg Bartunov
Date:
It's interesting, that Tom's example behaves different on my notebook:

8.02 (default optimization)
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                                  QUERY PLAN 
 

----------------------------------------------------------------------------------------------------------------------------------
IndexScan using tenk1_thous_tenthous on tenk1  (cost=0.00..142.91 rows=1 width=244) (actual time=0.369..7.378 rows=100
loops=1)  Index Cond: ((thousand >= 1) AND (thousand <= 100))   Filter: ((hundred >= 1) AND (hundred <= 10)) Total
runtime:8.100 ms
 
(4 rows)

CVS HEAD
regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
                                                      QUERY PLAN 
 

------------------------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on tenk1  (cost=20.14..236.96 rows=98 width=244) (actual time=3.116..6.857 rows=100 loops=1)   Recheck
Cond:((hundred >= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))   ->  BitmapAnd  (cost=20.14..20.14
rows=98width=0) (actual time=3.009..3.009 rows=0 loops=1)         ->  Bitmap Index Scan on tenk1_hundred
(cost=0.00..9.83rows=971 width=0) (actual time=1.497..1.497 rows=1000 loops=1)               Index Cond: ((hundred >=
1)AND (hundred <= 10))         ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..10.07 rows=1011 width=0)
(actualtime=1.179..1.179 rows=1000 loops=1)               Index Cond: ((thousand >= 1) AND (thousand <= 100)) Total
runtime:7.568 ms
 
(8 rows)


On Tue, 26 Apr 2005, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>> I tested bitmap scan and maybe I didnt find good examples, but with bitmap
>> scan is slower than hashjoin. Only when I use non otiptimized SELECT bps
>> was little bit faster. All my SELECTs are equal.
>
> Bitmap scans can't possibly be any faster for cases where the indexscan
> only fetches one row, which is true of all your test cases AFAICS.
>
> It should be at least marginally faster than the old code for cases
> involving overlapping ORed conditions, that is
>     WHERE some-indexable-condition OR some-other-indexable-condition
> where the conditions retrieve some of the same rows.
>
> But I think the real win will come on ANDing of distinct indexes, that
> is
>     WHERE condition-for-index-A AND condition-for-index-B
> where neither of the index conditions is individually very selective but
> together they select just a few rows.  Before, the optimizer could only
> choose one index or the other ... but now it can use both.
>
> An example in the regression database is
>
> regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
>                                                                QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on tenk1  (cost=19.91..234.07 rows=94 width=244) (actual time=7.372..8.560 rows=100 loops=1)
>   Recheck Cond: ((hundred >= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))
>   ->  BitmapAnd  (cost=19.91..19.91 rows=94 width=0) (actual time=7.094..7.094 rows=0 loops=1)
>         ->  Bitmap Index Scan on tenk1_hundred  (cost=0.00..9.62 rows=937 width=0) (actual time=3.210..3.210
rows=1000loops=1)
 
>               Index Cond: ((hundred >= 1) AND (hundred <= 10))
>         ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..10.04 rows=1007 width=0) (actual time=3.147..3.147
rows=1000loops=1)
 
>               Index Cond: ((thousand >= 1) AND (thousand <= 100))
> Total runtime: 9.505 ms
> (8 rows)
>
> In 8.0 this looks like
>
> regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
>                                              QUERY PLAN
> -------------------------------------------------------------------------------------------------------
> Seq Scan on tenk1  (cost=0.00..558.00 rows=99 width=244) (actual time=0.171..69.189 rows=100 loops=1)
>   Filter: ((hundred >= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))
> Total runtime: 70.013 ms
> (3 rows)
>
> The optimizer is a bit off on the relative merits of seqscan and
> indexscan for this case, but even the indexscan is not in the same
> ballpark, because it has to choose just one index to use:
>
> regression=# set enable_seqscan to 0;
> SET
> regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
>                                                          QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------
> Index Scan using tenk1_hundred on tenk1  (cost=0.00..1455.48 rows=99 width=244) (actual time=10.762..24.454 rows=100
loops=1)
>   Index Cond: ((hundred >= 1) AND (hundred <= 10))
>   Filter: ((thousand >= 1) AND (thousand <= 100))
> Total runtime: 25.384 ms
> (4 rows)
>
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Re: bitmapscan test, no success, bs is not faster

From
Oleg Bartunov
Date:
I didn't vacuum tenk1 after loading into 8.02, so optimizer was optimistic
and used index. After vacuuming I got what's Tom get.
On Tue, 26 Apr 2005, Tom Lane wrote:

>
> In 8.0 this looks like
>
> regression=# explain analyze select * from tenk1 where hundred between 1 and 10 and thousand between 1 and 100;
>                                              QUERY PLAN
> -------------------------------------------------------------------------------------------------------
> Seq Scan on tenk1  (cost=0.00..558.00 rows=99 width=244) (actual time=0.171..69.189 rows=100 loops=1)
>   Filter: ((hundred >= 1) AND (hundred <= 10) AND (thousand >= 1) AND (thousand <= 100))
> Total runtime: 70.013 ms
> (3 rows)
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Re: bitmapscan test, no success, bs is not faster

From
Pavel Stehule
Date:
On Tue, 26 Apr 2005, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> > I tested bitmap scan and maybe I didnt find good examples, but with bitmap 
> > scan is slower than hashjoin. Only when I use non otiptimized SELECT bps 
> > was little bit faster. All my SELECTs are equal.
> 
> Bitmap scans can't possibly be any faster for cases where the indexscan
> only fetches one row, which is true of all your test cases AFAICS.

yes, it's true. I found some selects where the benefit of bitmap scans is 
more clearly. There is only one small problem - optimizer didn't 
have to choose plan with bitmap scan in my examples. 

Thank you for explication, 
Regards
Pavel Stehule



Re: Tablepartitioning: Will it be supported in Future?

From
Robert Treat
Date:
There is a fairly lengthy discussion going on right now on the bizgres
mailing list about this topic, if your interested in helping out you
might want to join that list. 

Robert Treat

On Tue, 2005-04-26 at 05:43, apoc9009@yahoo.de wrote:
> Ok!
> The Links your posted are great and i guessing it will help me a lot!
> 
> But the other Question (if Tablepartitioning under Developemt and will 
> it be supported generally)
> is still alive.
> 
> Josh
> 
> Qingqing Zhou wrote:
> 
> ><apoc9009@yahoo.de> writes
> >  
> >
> >>I have done the following Post to PSQL Performance, but Richard Huxton
> >>say it
> >>might be a better Idea to post it again on you List so i forward the
> >>message.
> >>    
> >>
> >...
> >  
> >
> >>It is exiting to see thadt Verison 8.0 has Tablespaces like ORACLE and
> >>DB/2,
> >>but i need Partitioning on a few very large Tables.
> >>    
> >>
> >...
> >
> >Richard said you can *search* in this list ...  Here are two links might be
> >of your interests:
> >
> >http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php  - how
> >to do horizonal partition using inheritance;
> >
> >http://archives.postgresql.org/pgsql-performance/2005-03/msg00370.php - a
> >long list of discussing the future partition implementation;
> >
> >
> >Regards,
> >Qingqing
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >  
> >
>     *
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



Re: Tablepartitioning: Will it be supported in Future?

From
Hannu Krosing
Date:
On T, 2005-04-26 at 16:52 -0400, Robert Treat wrote:
> There is a fairly lengthy discussion going on right now on the bizgres
> mailing list about this topic, if your interested in helping out you
> might want to join that list. 

Wow! there is a BizGres mailinglist !?

And this is where the table-partitioning discussion is!

Where is this mailing list ? I found nothing about it on bizgres
website.

I'd be definitely interested!

-- 
Hannu Krosing <hannu@tm.ee>


Re: Tablepartitioning: Will it be supported in Future?

From
Josh Berkus
Date:
Hannu,

> > There is a fairly lengthy discussion going on right now on the bizgres
> > mailing list about this topic, if your interested in helping out you
> > might want to join that list.
>
> Wow! there is a BizGres mailinglist !?
>
> And this is where the table-partitioning discussion is!
>
> Where is this mailing list ? I found nothing about it on bizgres
> website.

Yes, it's under Developer/Projects.   I need to provide more links on stuff.

>
> I'd be definitely interested!

http://www.pgfoundry.org/projects/bizgres

--Josh

-- 
__Aglio Database Solutions_______________
Josh Berkus               Consultant
josh@agliodbs.com     www.agliodbs.com
Ph: 415-752-2500    Fax: 415-752-2387
2166 Hayes Suite 200    San Francisco, CA


Re: Tablepartitioning: Will it be supported in Future?

From
Hannu Krosing
Date:
On K, 2005-04-27 at 15:40 -0700, Josh Berkus wrote:
> Hannu,
> 
> > > There is a fairly lengthy discussion going on right now on the bizgres
> > > mailing list about this topic, if your interested in helping out you
> > > might want to join that list.
> >
> > Wow! there is a BizGres mailinglist !?
> >
> > And this is where the table-partitioning discussion is!
> >
> > Where is this mailing list ? I found nothing about it on bizgres
> > website.
> 
> Yes, it's under Developer/Projects.   I need to provide more links on stuff.

Jap. Already found and subscribed once I learned that I have to search
under bizgres ;) . 

Thanks!

-- 
Hannu Krosing <hannu@skype.net>



Re: Tablepartitioning: Will it be supported in Future?

From
Christopher Kings-Lynne
Date:
> There is a fairly lengthy discussion going on right now on the bizgres
> mailing list about this topic, if your interested in helping out you
> might want to join that list. 

What's the point of keeping such backend development discussion separate 
from the -hackers list?  It's always been a mistake in the past...

Chris


Re: Tablepartitioning: Will it be supported in Future?

From
Bruce Momjian
Date:
Christopher Kings-Lynne wrote:
> > There is a fairly lengthy discussion going on right now on the bizgres
> > mailing list about this topic, if your interested in helping out you
> > might want to join that list. 
> 
> What's the point of keeping such backend development discussion separate 
> from the -hackers list?  It's always been a mistake in the past...

That was my impression too.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Tablepartitioning: Will it be supported in Future?

From
Neil Conway
Date:
Christopher Kings-Lynne wrote:
> What's the point of keeping such backend development discussion separate 
> from the -hackers list?  It's always been a mistake in the past...

Yeah, it struck me as a bad idea as well.

-Neil