Tablepartitioning: Will it be supported in Future?

<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 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=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.147 rows=1000 loops=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

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
----------------------------------------------------------------------------------------------------------------------------------
Index Scan 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
------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap 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=98 width=0) (actual time=3.009..3.009 rows=0 loops=1)
-> Bitmap Index Scan on tenk1_hundred (cost=0.00..9.83 rows=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) (actual time=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=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.147 rows=1000 loops=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

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

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, Pennsylvania 19073

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