Thread

1. Re: Speed Question

   Noah Silverman <noah@allresearch.com> — 2002-12-21T00:10:49Z

   First:  THANK YOU everyone for all  your suggestions.
   
   I've discovered the "copy from" command and it helps a lot.
   Right now, we just ran a test on 1MM rows with 4 columns and it is very 
   fast with a 4 column index.  Works well.
   
   Now we are creating more of a real world example: 10MM rows with 32 
   columns of integers.  I'm loading up the data now, and will create a 
   multi-column index(on all 32) after the data is loaded.
   
    From everyone's responses I understand that we really need to tune the 
   system to get optimal performance.  I would love to do this, but don't 
   really know where to start.  Below are our system stats if anyone wants 
   to suggest some settings:
   
   2x AMD 2100MP CPU
   2 GB RAM
   Data - 350GB on a raid5 card
   Note: We will probably NEVER use transactions, so turning off that 
   feature would be fine if it would help, and we knew how.
   
   Our data is probably only going to take up 20% MAXIMUM of our RAID.  
   Subsequently, we have no problem trading a little extra space for 
   better performance.
   
   BTW - is there any kind of "describe table" and/or "show index" 
   function if pgsql.  I've gotten very used to them in Mysql, but they 
   don't work here.  There must be some way.  I've RTFM, but can't find 
   anything.  help.
   
   THANKS AGAIN,
   
   -Noah
   
   
   
   

2. Re: Speed Question

   Philip Hallstrom <philip@adhesivemedia.com> — 2002-12-21T01:01:13Z

   > BTW - is there any kind of "describe table" and/or "show index"
   > function if pgsql.  I've gotten very used to them in Mysql, but they
   > don't work here.  There must be some way.  I've RTFM, but can't find
   > anything.  help.
   
   In psql use "\d tablename".  do a "\?" for a quick overview and "man psql"
   for lots of stuff.
   
   -philip
   
   
   

3. Re: Speed Question

   Manfred Koizar <mkoi-pg@aon.at> — 2002-12-21T12:21:31Z

   On Fri, 20 Dec 2002 19:10:49 -0500, Noah Silverman
   <noah@allresearch.com> wrote:
   >Now we are creating more of a real world example: 10MM rows with 32 
   >columns of integers.  I'm loading up the data now, and will create a 
   >multi-column index(on all 32) after the data is loaded.
   
   If a table with a 32 column key and no dependent attributes is a real
   world example, I'd like to see your use case ;-)
   
   An index on c1, c2, ..., cn will only help, if your search criteria
   contain (strict) conditions on the leading index columns, e.g.
   	WHERE c1 = ... AND c2 = ... AND c3 BETWEEN ... AND ...
   
   It won't help for 
   	WHERE c22 = ...
   
   > From everyone's responses I understand that we really need to tune [...]
   >2x AMD 2100MP CPU
   >2 GB RAM
   >Data - 350GB on a raid5 card
   
   It all depends on your application, but looking at SHARED_BUFFERS,
   EFFECTIVE_CACHE_SIZE, SORT_MEM, MAX_FSM_RELATIONS, and MAX_FSM_PAGES
   might be a good start.  Later you might want to use CPU_*_COST,
   RANDOM_PAGE_COST, and various WAL settings to fine tune your system.
   
   >Note: We will probably NEVER use transactions,
   
   Oh yes, you will.  You have no other choice.  If you don't enclose
   (several) statements between BEGIN and COMMIT, every statement is
   automatically wrapped into its own transaction.
   
   It helps performance and consistency, if *you* control transactions.
   
   Servus
    Manfred
   
   

4. Re: Speed Question

   Noah Silverman <noah@allresearch.com> — 2002-12-21T18:46:05Z

   Thanks for the help.  We've been using MySQL for the last 4 years, so 
   PgSQL is a whole new world for me.  Lots to learn
   
   Actually the "real world" test we are performing is an exact 
   duplication of our intended use.  Without divulging too many company 
   secrets, we create a 32 key profile of an object.  We then have to be 
   able to search the database to  find "similar" objects.  In reality, we 
   will probably have 20MM to 30MM rows in our table.  I need to very 
   quickly find the matching records on a "test" object.
   
   If you're really curious as to more details, let me know (I don't want 
   to bore the group with our specifics)
   
   Since this machine is solely a database server, I want to utilize a ton 
   of RAM to help things along.  Probably at lease 1.5 Gigs worth.  I 
   guess my next step is to try and figure out what all the various memory 
   settings are and where to set them.
   
   Thanks,
   
   -N
   
   
   On Saturday, December 21, 2002, at 07:21  AM, Manfred Koizar wrote:
   
   > On Fri, 20 Dec 2002 19:10:49 -0500, Noah Silverman
   > <noah@allresearch.com> wrote:
   >> Now we are creating more of a real world example: 10MM rows with 32
   >> columns of integers.  I'm loading up the data now, and will create a
   >> multi-column index(on all 32) after the data is loaded.
   >
   > If a table with a 32 column key and no dependent attributes is a real
   > world example, I'd like to see your use case ;-)
   >
   > An index on c1, c2, ..., cn will only help, if your search criteria
   > contain (strict) conditions on the leading index columns, e.g.
   > 	WHERE c1 = ... AND c2 = ... AND c3 BETWEEN ... AND ...
   >
   > It won't help for
   > 	WHERE c22 = ...
   >
   >> From everyone's responses I understand that we really need to tune 
   >> [...]
   >> 2x AMD 2100MP CPU
   >> 2 GB RAM
   >> Data - 350GB on a raid5 card
   >
   > It all depends on your application, but looking at SHARED_BUFFERS,
   > EFFECTIVE_CACHE_SIZE, SORT_MEM, MAX_FSM_RELATIONS, and MAX_FSM_PAGES
   > might be a good start.  Later you might want to use CPU_*_COST,
   > RANDOM_PAGE_COST, and various WAL settings to fine tune your system.
   >
   >> Note: We will probably NEVER use transactions,
   >
   > Oh yes, you will.  You have no other choice.  If you don't enclose
   > (several) statements between BEGIN and COMMIT, every statement is
   > automatically wrapped into its own transaction.
   >
   > It helps performance and consistency, if *you* control transactions.
   >
   > Servus
   >  Manfred
   >
   >
   
   
   

5. Re: Speed Question

   Manfred Koizar <mkoi-pg@aon.at> — 2002-12-21T20:02:39Z

   On Sat, 21 Dec 2002 13:46:05 -0500, Noah Silverman
   <noah@allresearch.com> wrote:
   >Without divulging too many company 
   >secrets, we create a 32 key profile of an object.  We then have to be 
   >able to search the database to  find "similar" objects.
   
   ... where "similar" means that the value of each attribute lies within
   a small range around the value of the corresponding attribute of the
   reference object?
   
   I fear a multicolumn b-tree index is not the optimal solution to this
   problem, unless you have some extremely selective attributes you can
   put at the start of the index.  But then again I doubt that it makes
   sense to include even the last attribute (or the last few attributes)
   into the index.
   
   >In reality, we 
   >will probably have 20MM to 30MM rows in our table.  I need to very 
   >quickly find the matching records on a "test" object.
   
   This seems to be a nice case for utilizing bitmaps for index scans.
   Thus you would scan several single column indices and combine the
   bitmaps before accessing the heap tuples.  This has been discussed on
   -hackers and I believe it is a todo item.
   
   I don't know, whether GiST or R-Tree could help.  Is anybody listening
   who knows?
   
   >If you're really curious as to more details, let me know (I don't want 
   >to bore the group with our specifics)
   
   The group is patient :-)
   
   Servus
    Manfred
   
   

6. Re: Speed Question

   Noah Silverman <noah@allresearch.com> — 2002-12-21T20:17:53Z

   You are correct.  "similar" means within a small range.
   
   Below is a sample query:
   
   select count(*) from speedtest where (p1 between 209 and 309) and (p2 
   between 241 and 341) and (p3 between 172 and 272) and (p4 between 150 
   and 250) and (p5 between 242 and 342) and (p6 between 222 and 322) and 
   (p7 between 158 and 258) and (p8 between 249 and 349) and (p9 between 
   162 and 262) and (p10 between 189 and 289) and (p11 between 201 and 
   301) and (p12 between 167 and 267) and (p13 between 167 and 267) and 
   (p14 between 229 and 329) and (p15 between 235 and 335) and (p16 
   between 190 and 290) and (p17 between 240 and 340) and (p18 between 156 
   and 256) and (p19 between 150 and 250) and (p20 between 171 and 271) 
   and (p21 between 241 and 341) and (p22 between 244 and 344) and (p23 
   between 219 and 319) and (p24 between 198 and 298) and (p25 between 196 
   and 296) and (p26 between 243 and 343) and (p27 between 160 and 260) 
   and (p28 betw een 151 and 251) and (p29 between 226 and 326) and (p30 
   between 168 and 268) and (p31  between 153 and 253) and (p32 between 
   218 and 318)
   
   Currently, on an un-tuned installation, this query takes about 1 
   second.  Much too slow for our needs.  We need to be able to execute 
   about 30-50 per second.
   
   
   I'm not a database expert.  There is probably a better way to do this, 
   but I have no idea how.
   
   The general use of this table is as an index for document storage.  
   When we come across a new document, we have to know if we already have 
   something close to it.  Exact checksums don't work because two 
   documents with only a few different words are still "the same" for our 
   intended use.  We calculate 32 separate checksums on parts of each 
   document.   By storing all 32, we have a good representation of each 
   document.  A new document can then very quickly be checked against the 
   table to see if we already have something close to it.
   
   If anybody has any better ideas, I would love to hear it...
   
   -N
   
   
   On Saturday, December 21, 2002, at 03:02  PM, Manfred Koizar wrote:
   
   > On Sat, 21 Dec 2002 13:46:05 -0500, Noah Silverman
   > <noah@allresearch.com> wrote:
   >> Without divulging too many company
   >> secrets, we create a 32 key profile of an object.  We then have to be
   >> able to search the database to  find "similar" objects.
   >
   > ... where "similar" means that the value of each attribute lies within
   > a small range around the value of the corresponding attribute of the
   > reference object?
   >
   > I fear a multicolumn b-tree index is not the optimal solution to this
   > problem, unless you have some extremely selective attributes you can
   > put at the start of the index.  But then again I doubt that it makes
   > sense to include even the last attribute (or the last few attributes)
   > into the index.
   >
   >> In reality, we
   >> will probably have 20MM to 30MM rows in our table.  I need to very
   >> quickly find the matching records on a "test" object.
   >
   > This seems to be a nice case for utilizing bitmaps for index scans.
   > Thus you would scan several single column indices and combine the
   > bitmaps before accessing the heap tuples.  This has been discussed on
   > -hackers and I believe it is a todo item.
   >
   > I don't know, whether GiST or R-Tree could help.  Is anybody listening
   > who knows?
   >
   >> If you're really curious as to more details, let me know (I don't want
   >> to bore the group with our specifics)
   >
   > The group is patient :-)
   >
   > Servus
   >  Manfred
   >
   >
   
   
   

7. Re: Speed Question

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-21T20:28:22Z

   Manfred Koizar <mkoi-pg@aon.at> writes:
   > ... where "similar" means that the value of each attribute lies within
   > a small range around the value of the corresponding attribute of the
   > reference object?
   
   > I don't know, whether GiST or R-Tree could help.
   
   If the problem is multidimensional range search then GIST might be just
   the ticket.  I am not sure if you'd need to do any coding though.  It
   looks like contrib/btree_gist provides the necessary operator class, but
   only for int4 and timestamp datatypes.
   
   I think that our r-tree code is restricted to two-dimensional indexing,
   so it wouldn't help.
   
   			regards, tom lane
   
   

8. Re: Speed Question

   Noah Silverman <noah@allresearch.com> — 2002-12-23T21:55:01Z

   Does anyone know how/where I can find the contrib/btree_gist stuff and 
   how I use it, and are there docs for it.
   
   Thanks,
   
   -N
   
   
   On Saturday, December 21, 2002, at 03:28 PM, Tom Lane wrote:
   
   > Manfred Koizar <mkoi-pg@aon.at> writes:
   >> ... where "similar" means that the value of each attribute lies within
   >> a small range around the value of the corresponding attribute of the
   >> reference object?
   >
   >> I don't know, whether GiST or R-Tree could help.
   >
   > If the problem is multidimensional range search then GIST might be just
   > the ticket.  I am not sure if you'd need to do any coding though.  It
   > looks like contrib/btree_gist provides the necessary operator class, 
   > but
   > only for int4 and timestamp datatypes.
   >
   > I think that our r-tree code is restricted to two-dimensional indexing,
   > so it wouldn't help.
   >
   > 			regards, tom lane
   >