Thread: Tablepartitioning: Will it be supported in Future?
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
<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
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 > > > *
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
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
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
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
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
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
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
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>
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
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>
> 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
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
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