Horton

Thread

  1. Tablepartitioning: Will it be supported in Future?

    apoc9009@yahoo.de <apoc9009@yahoo.de> — 2005-04-26T08:32:58Z

    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

  2. Re: Tablepartitioning: Will it be supported in Future?

    Qingqing Zhou <zhouqq@cs.toronto.edu> — 2005-04-26T09:25:12Z

    <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

  3. Re: Tablepartitioning: Will it be supported in Future?

    apoc9009@yahoo.de <apoc9009@yahoo.de> — 2005-04-26T09:43:21Z

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

  4. bitmapscan test, no success, bs is not faster

    Pavel Stehule <stehule@kix.fsv.cvut.cz> — 2005-04-26T11:20:38Z

    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

  5. Re: bitmapscan test, no success, again

    Pavel Stehule <stehule@kix.fsv.cvut.cz> — 2005-04-26T12:05:59Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-04-26T15:15:35Z

    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

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

    Oleg Bartunov <oleg@sai.msu.su> — 2005-04-26T16:31:23Z

    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

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

    Oleg Bartunov <oleg@sai.msu.su> — 2005-04-26T16:35:24Z

    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

  9. Re: Tablepartitioning: Will it be supported in Future?

    Robert Treat <xzilla@users.sourceforge.net> — 2005-04-26T20:52:35Z

    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

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

    Pavel Stehule <stehule@kix.fsv.cvut.cz> — 2005-04-27T06:27:02Z

    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

  11. Re: Tablepartitioning: Will it be supported in Future?

    Hannu Krosing <hannu@tm.ee> — 2005-04-27T22:22:55Z

    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>

  12. Re: Tablepartitioning: Will it be supported in Future?

    Josh Berkus <josh@agliodbs.com> — 2005-04-27T22:40:44Z

    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

  13. Re: Tablepartitioning: Will it be supported in Future?

    Hannu Krosing <hannu@skype.net> — 2005-04-27T22:43:45Z

    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>

  14. Re: Tablepartitioning: Will it be supported in Future?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-04-28T01:16:47Z

    > 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

  15. Re: Tablepartitioning: Will it be supported in Future?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-04-28T01:22:32Z

    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

  16. Re: Tablepartitioning: Will it be supported in Future?

    Neil Conway <neilc@samurai.com> — 2005-04-28T01:43:09Z

    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