Re: [PERFORM] simple join uses indexes, very slow
On Mon, 2006-03-27 at 13:47 -0500, george young wrote:
> Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
>
> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro,
> parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and
> ro.run='team9';
> QUERY PLAN
>
> Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
> time=14.986..70197.129 rows=43050 loops=1)
>-> Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
> rows=71 width=18) (actual time=0.386..62.959 rows=263 loops=1)
> Index Cond: (run = 'team9'::text)
>-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
> (actual time=1.591..266.211 rows=164 loops=263)
> Recheck Cond: (('team9'::text = p.run) AND ("outer".opset_num =
> p.opset_num))
> -> Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
> width=0) (actual time=1.153..1.153 rows=164 loops=263)
>Index Cond: (('team9'::text = p.run) AND ("outer".opset_num =
> p.opset_num))
> Total runtime: 70237.727 ms
> (8 rows)
The planner appears to be underestimating the number of rows retrieved
in both cases, then multiplying them together to make it worse.
Multi-column indexes provide less accurate estimates (right now).
Looks like a hash join might be faster. What is your work_mem set to?
Can you SET enable_nestloop=off and rerun the EXPLAIN ANALYZE?
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] count(*) performance
Gábriel Ákos wrote: > I thought that too. Autovacuum is running on our system but it didn't do > the trick. Anyway the issue is solved, thank you all for helping. :) Hi, Gabriel, it may be that your Free Space Map (FSM) setting is way to low. Try increasing it. Btw, VACUUM outputs a Warning if FSM is not high enough, maybe you can find useful hints in the log file. HTH Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Slow performance on Windows .NET and OleDb
Hello, I have just installed PostGreSql 8.1 on my Windows XP PC. I created a simple table called users with 4 varchar fields. I am using the OleDb connection driver. In my .NET application, I populate 3000 records into the table to test PostGreSql's speed. It takes about 3-4 seconds. Even worse is displaying the 3000 records in a ListView control. It takes about 7 seconds. In MySQL, the exact same table and application displays the same 3000 records in under 1/2 second!!! Why is PostGreSql so slow compared to MySQL? What do you recommend I do to speed up? It is such a simple query and small database. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Slow performance on Windows .NET and OleDb
On 3/28/06, Greg Quinn <[EMAIL PROTECTED]> wrote: > I am using the OleDb connection driver. In my .NET application, I populate > 3000 records into the table to test PostGreSql's speed. It takes about 3-4 > seconds. have you tried: 1. npgsql .net data provider 2. odbc ado.net bridge merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
On Tue, 28 Mar 2006 09:30:54 +0100
Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins:
> On Mon, 2006-03-27 at 13:47 -0500, george young wrote:
>
> > Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
> >
> > explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro,
> > parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and
> > ro.run='team9';
> > QUERY PLAN
> >
> > Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
> > time=14.986..70197.129 rows=43050 loops=1)
> >-> Index Scan using run_opsets_pkey on run_opsets ro
> > (cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959 rows=263
> > loops=1)
> > Index Cond: (run = 'team9'::text)
> >-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
> > width=22) (actual time=1.591..266.211 rows=164 loops=263)
> > Recheck Cond: (('team9'::text = p.run) AND ("outer".opset_num =
> > p.opset_num))
> > -> Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
> > width=0) (actual time=1.153..1.153 rows=164 loops=263)
> >Index Cond: (('team9'::text = p.run) AND ("outer".opset_num
> > = p.opset_num))
> > Total runtime: 70237.727 ms
> > (8 rows)
>
> The planner appears to be underestimating the number of rows retrieved
> in both cases, then multiplying them together to make it worse.
> Multi-column indexes provide less accurate estimates (right now).
>
> Looks like a hash join might be faster. What is your work_mem set to?
work_mem= 1024
> Can you SET enable_nestloop=off and rerun the EXPLAIN ANALYZE?
newschm3=> set enable_nestloop=off ;
SET
newschm3=> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM
run_opsets ro, parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num
and ro.run='team9';
QUERY PLAN
Merge Join (cost=34177.87..34291.36 rows=6707 width=22) (actual
time=68421.681..68547.686 rows=43050 loops=1)
Merge Cond: ("outer".opset_num = "inner".opset_num)
-> Sort (cost=130.93..131.11 rows=71 width=18) (actual
time=107.744..107.901 rows=263 loops=1)
Sort Key: ro.opset_num
-> Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=57.641..106.096 rows=263
loops=1)
Index Cond: (run = 'team9'::text)
-> Sort (cost=34046.94..34070.02 rows=9231 width=22) (actual
time=68301.325..68358.087 rows=43050 loops=1)
Sort Key: p.opset_num
-> Bitmap Heap Scan on parameters p (cost=272.31..33438.97 rows=9231
width=22) (actual time=526.462..67363.577 rows=43050 loops=1)
Recheck Cond: ('team9'::text = run)
-> Bitmap Index Scan on parameters_idx (cost=0.00..272.31
rows=9231 width=0) (actual time=483.500..483.500 rows=43050 loops=1)
Index Cond: ('team9'::text = run)
Total runtime: 68595.868 ms
(13 rows)
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] simple join uses indexes, very slow
Hi, George, george young wrote: >>Looks like a hash join might be faster. What is your work_mem set to? > > work_mem= 1024 This is 1 Megabyte. By all means, increase it, if possible. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Massive Inserts Strategies
I tried this solution, but ran into following problem. The temp_table has columns (col1, col2, col3). The original_table has columns (col0, col1, col2, col3) Now the extra col0 on the original_table is the unique generated ID by the database. How can I make your suggestions work in that case .. ? On Wednesday 22 March 2006 11:09 am, Jim C. Nasby wrote: > Load the files into a temp table and go from there... > > COPY ... FROM file; > UPDATE existing_table SET ... WHERE ...; > INSERT INTO existing_table SELECT * FROM temp_table WHERE NOT EXISTS( > SELECT * FROM existing_table WHERE ...) > > On Wed, Mar 22, 2006 at 10:32:10AM -0500, ashah wrote: > > I have a database with foreign keys enabled on the schema. I receive > > different files, some of them are huge. And I need to load these files in > > the database every night. There are several scenerios that I want to > > design an optimal solution for - > > > > 1. One of the file has around 80K records and I have to delete everything > > from the table and load this file. The provider never provides a "delta > > file" so I dont have a way to identify which records are already present > > and which are new. If I dont delete everything and insert fresh, I have > > to make around 80K selects to decide if the records exist or not. Now > > there are lot of tables that have foreign keys linked with this table so > > unless I disable the foreign keys, I cannot really delete anything from > > this table. What would be a good practise here? > > > > 2. Another file that I receive has around 150K records that I need to > > load in the database. Now one of the fields is logically a "foreign key" > > to another table, and it is linked to the parent table via a database > > generated unique ID instead of the actual value. But the file comes with > > the actual value. So once again, I have to either drop the foreign key, > > or make 150K selects to determine the serial ID so that the foreign key > > is satisfied. What would be a good strategy in this scenerio ? > > > > Please pardon my inexperience with database ! > > > > Thanks, > > Amit > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Massive Inserts Strategies
Hi, ashah, ashah wrote: > I tried this solution, but ran into following problem. > > The temp_table has columns (col1, col2, col3). > > The original_table has columns (col0, col1, col2, col3) > Now the extra col0 on the original_table is the unique generated ID by > the database. INSERT INTO original_table (col1, col2, col3) SELECT col1, col2, col3 FROM temp_table WHERE ... HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] simple join uses indexes, very slow
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of george young > Sent: Monday, March 27, 2006 12:48 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] simple join uses indexes, very slow > [Snip] > > Indexes: > "parameters_idx" btree (run, opset_num, step_num, opset, opset_ver, > step, step_ver, name, split, wafers) > "parameters_opset_idx" btree (opset, step, name) > "parameters_step_idx" btree (step, name) > Have you tried creating some different indexes on parameters? I don't know if it should matter or not, but I would try some indexes like: (run, opset_num) //Without all the other columns (opset_num, run) //Backwards (opset_num) I don't really know Postgres internals all that well. It just seems to me that parameters_idx has a lot of columns this query is not interested in. I'd just be curious to see what happens. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] simple join uses indexes, very slow
On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote: >> "parameters_idx" btree (run, opset_num, step_num, opset, > opset_ver, >> step, step_ver, name, split, wafers) >> "parameters_opset_idx" btree (opset, step, name) >> "parameters_step_idx" btree (step, name) > Have you tried creating some different indexes on parameters? I don't > know if it should matter or not, but I would try some indexes like: > > (run, opset_num) //Without all the other columns > (opset_num, run) //Backwards > (opset_num) An index on (A,B,C) can be used for a query on (A,B) or (A), so it doesn't really matter. It isn't usable for a query on (B), (C) or (B,C), though. (The index rows will get bigger, of course, so you'll need more I/O if you want to scan large parts of it, but I guess that's beside the point.) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson > Sent: Tuesday, March 28, 2006 10:29 AM > > An index on (A,B,C) can be used for a query on (A,B) or (A), so it doesn't > really matter. It isn't usable for a query on (B), (C) or (B,C), though. > (The > index rows will get bigger, of course, so you'll need more I/O if you want > to > scan large parts of it, but I guess that's beside the point.) I guess what I am really curious about is why was the OP getting an expensive sort when the planner tried a merge join? Most of the time was spent sorting the parameters parameters table by opset_num even though opset_num is indexed. Isn't Postgres able to walk the index instead of sorting? I was wondering if maybe Postgres wasn't recognizing that it could just walk the index because the opset_num column isn't the first in the index. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] count(*) performance
On Mon, Mar 27, 2006 at 12:20:54PM -0700, Brendan Duddridge wrote: > Does that mean that even though autovacuum is turned on, you still > should do a regular vacuum analyze periodically? Doing a periodic vacuumdb -avz and keeping an eye on the last few lines isn't a bad idea. It would also be helpful if there was a log parser that could take a look at the output of a vacuumdb -av and look for any problem areas, such as relations that have a lot of free space in them. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
On Tue, Mar 28, 2006 at 06:29:08PM +0200, Steinar H. Gunderson wrote: > On Tue, Mar 28, 2006 at 10:18:25AM -0600, Dave Dutcher wrote: > >> "parameters_idx" btree (run, opset_num, step_num, opset, > > opset_ver, > >> step, step_ver, name, split, wafers) > >> "parameters_opset_idx" btree (opset, step, name) > >> "parameters_step_idx" btree (step, name) > > Have you tried creating some different indexes on parameters? I don't > > know if it should matter or not, but I would try some indexes like: > > > > (run, opset_num) //Without all the other columns > > (opset_num, run) //Backwards > > (opset_num) > > An index on (A,B,C) can be used for a query on (A,B) or (A), so it doesn't > really matter. It isn't usable for a query on (B), (C) or (B,C), though. (The > index rows will get bigger, of course, so you'll need more I/O if you want to > scan large parts of it, but I guess that's beside the point.) Note that given how statistics currenly work, there are many situations where the planner will refuse to use a multi-column index. This probably won't change until there's some concept of multi-column statistics, at least for multi-column indexes. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Slow performance on Windows .NET and OleDb
On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote: > Hello, > > I have just installed PostGreSql 8.1 on my Windows XP PC. I created a > simple table called users with 4 varchar fields. > > I am using the OleDb connection driver. In my .NET application, I populate > 3000 records into the table to test PostGreSql's speed. It takes about 3-4 > seconds. > > Even worse is displaying the 3000 records in a ListView control. It takes > about 7 seconds. In MySQL, the exact same table and application displays > the same 3000 records in under 1/2 second!!! Have you vacuumed recently? This smells like it might be a table bloat problem. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] simple join uses indexes, very slow
On Tue, Mar 28, 2006 at 11:20:19AM -0600, Dave Dutcher wrote: > I guess what I am really curious about is why was the OP getting an > expensive sort when the planner tried a merge join? A merge join requires sorted inputs. > Most of the time was spent sorting the parameters parameters table by > opset_num even though opset_num is indexed. Isn't Postgres able to walk the > index instead of sorting? The time of an index scan vs. a sequential scan + sort depends on several factors, so it's not just a matter of walking the index whenever there is one. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Massive Inserts Strategies
Is there some other unique key you can test on? Take a look at http://lnk.nu/cvs.distributed.net/8qt.sql lines 169-216 for an exammple. In this case we use a different method for assigning IDs than you probably will, but the idea remains. On Tue, Mar 28, 2006 at 10:59:49AM -0500, ashah wrote: > I tried this solution, but ran into following problem. > > The temp_table has columns (col1, col2, col3). > > The original_table has columns (col0, col1, col2, col3) > > Now the extra col0 on the original_table is the unique generated ID by the > database. > > How can I make your suggestions work in that case .. ? > > On Wednesday 22 March 2006 11:09 am, Jim C. Nasby wrote: > > Load the files into a temp table and go from there... > > > > COPY ... FROM file; > > UPDATE existing_table SET ... WHERE ...; > > INSERT INTO existing_table SELECT * FROM temp_table WHERE NOT EXISTS( > > SELECT * FROM existing_table WHERE ...) > > > > On Wed, Mar 22, 2006 at 10:32:10AM -0500, ashah wrote: > > > I have a database with foreign keys enabled on the schema. I receive > > > different files, some of them are huge. And I need to load these files in > > > the database every night. There are several scenerios that I want to > > > design an optimal solution for - > > > > > > 1. One of the file has around 80K records and I have to delete everything > > > from the table and load this file. The provider never provides a "delta > > > file" so I dont have a way to identify which records are already present > > > and which are new. If I dont delete everything and insert fresh, I have > > > to make around 80K selects to decide if the records exist or not. Now > > > there are lot of tables that have foreign keys linked with this table so > > > unless I disable the foreign keys, I cannot really delete anything from > > > this table. What would be a good practise here? > > > > > > 2. Another file that I receive has around 150K records that I need to > > > load in the database. Now one of the fields is logically a "foreign key" > > > to another table, and it is linked to the parent table via a database > > > generated unique ID instead of the actual value. But the file comes with > > > the actual value. So once again, I have to either drop the foreign key, > > > or make 150K selects to determine the serial ID so that the foreign key > > > is satisfied. What would be a good strategy in this scenerio ? > > > > > > Please pardon my inexperience with database ! > > > > > > Thanks, > > > Amit > > > > > > ---(end of broadcast)--- > > > TIP 4: Have you searched our list archives? > > > > > >http://archives.postgresql.org > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow performance on Windows .NET and OleDb
Jim C. Nasby wrote: On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote: Hello, I have just installed PostGreSql 8.1 on my Windows XP PC. I created a simple table called users with 4 varchar fields. I am using the OleDb connection driver. In my .NET application, I populate 3000 records into the table to test PostGreSql's speed. It takes about 3-4 seconds. Even worse is displaying the 3000 records in a ListView control. It takes about 7 seconds. In MySQL, the exact same table and application displays the same 3000 records in under 1/2 second!!! Have you vacuumed recently? This smells like it might be a table bloat problem. This could be a lot of things... He is probably running the default postgresql.conf which is going to perform horribly. What is your work_mem? shared_buffers? Are you passing a where clause? If so is there an index on the field that is subject to the clause? When you do the population, is it via inserts or copy? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] simple join uses indexes, very slow
On Tue, 2006-03-28 at 10:22 -0500, george young wrote: > work_mem= 1024 Set that higher. Try a couple of other plans using enable_* and let us have the EXPLAIN ANALYZE plans. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Decide between Postgresql and Mysql (help of comunity)
Hi, I'm a Postgresql's user and I think that it's very very good and robust. In my work we're confuse between where database is the best choose: Postgresql or Mysql. The Mysql have the reputation that is very fast working in the web but in our application we are estimating many access simultaneous, then I think that the Postgresql is the best choice. Am I right? Our server have 1 GB of RAM, how many users can it support at the same time with this memory? Thanks in advanced Marcos ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Decide between Postgresql and Mysql (help of comunity)
Marcos wrote: > Hi, > > I'm a Postgresql's user and I think that it's very very good and > robust. > > In my work we're confuse between where database is the best choose: > Postgresql or Mysql. The Mysql have the reputation that is very fast > working in the web but in our application we are estimating many access > simultaneous, then I think that the Postgresql is the best choice. > > Am I right? > > Our server have 1 GB of RAM, how many users can it support at the same > time with this memory? > > Thanks in advanced > > Marcos The RAM/users question depends largely on what the database is used for and what each user is doing in the database. From what I understand, PostgreSQL is designed with stability and reliability as key tenants. MySQL favors performance and ease of use. An example is that, last I checked, MySQL doesn't have an equivalent to PostgreSQL's 'fsync' which helps insure that data is actually written to the disk. This costs performance but increases reliability and crash recovery. HTH Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Decide between Postgresql and Mysql (help of
On Tue, 2006-03-28 at 09:31, Marcos wrote: > Hi, > > I'm a Postgresql's user and I think that it's very very good and > robust. > > In my work we're confuse between where database is the best choose: > Postgresql or Mysql. The Mysql have the reputation that is very fast > working in the web but in our application we are estimating many access > simultaneous, then I think that the Postgresql is the best choice. > > Am I right? > > Our server have 1 GB of RAM, how many users can it support at the same > time with this memory? This is as much about the code in front of the database as the database itself. You'll want to use an architecture that supports pooled connections (java, php under lighttpd, etc...) and you'll want to look at your read to write ratio. MySQL and PostgreSQL can handle fairly heavy parallel loads. PostgreSQL is generally a much better performer when you need to make a lot of parallel writes. But the bigger question is which one is suited to your application in general. If some major issue in MySQL or PostgreSQL makes it a poor choice for your app, then it doesn't matter how much load it can handle, it's still a poor choice. Generally speaking, MySQL is a poor choice if you're doing things like accounting, where the maths have to be correct. It's quite easy to ask MySQL to do math and get the wrong answer. It also has some serious problems with referential integrity, but most of those can be worked around using innodb tables. But at that point, you're using the same basic storage methods as PostgreSQL uses, i.e. an MVCC storage engine. And now that Oracle has bought Innodb, the availability of that in the future to MySQL is in doubt. There's also the issue of licensing. If you'll be selling copies of your app to customers, you'll be writing a check for each install to MySQL AB. Not so with PostgreSQL. So, what exactly are you planning on doing? Lastly, take a look here: http://sql-info.de/mysql/gotchas.html and here: http://sql-info.de/postgresql/postgres-gotchas.html for a list of the common "gotchas" in both databases. Generally you'll find the PostgreSQL gotchas are of the sort that make you go "oh, that's interesting" and the MySQL gotchas are the kind that make you go "Dear god, you must be kidding me!" But that's just my opinion, I could be wrong. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] MVCC intro and benefits docs?
Hi, Does anyone know of any fairly entry-level documentation for the benefits-drawbacks of MVCC in the db? As it relates to performance? Postgres vs the others? Cheers Antoine -- This is where I should put some witty comment. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Decide between Postgresql and Mysql (help of
> So, what exactly are you planning on doing? The application will be a chat for web, the chats will be stored in the server. In a determined interval of time... more or less 2 seconds, the application will be looking for new messages. I believe that it will make many accesses. The write in disc will be constant. Thanks :o) Marcos ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Decide between Postgresql and Mysql (help of
On Tue, 2006-03-28 at 13:42, PFC wrote: > > This is as much about the code in front of the database as the database > > itself. You'll want to use an architecture that supports pooled > > connections (java, php under lighttpd, etc...) and you'll want to look > > Well, anybody who uses PHP and cares about performance is already using > > lighttpd, no ? > > > MySQL and PostgreSQL can handle fairly heavy parallel loads. > > I'll only speak about MyISAM. MySQL == MyISAM. InnoDB is useless : if > you > want transactions, use postgres. I agree with most of what you posted, but I'm not quite sure what you meant here. Innodb in and of itself is a fairly decent MVCC implementation, with, as usual, some limitations (it's rollback performance is HORRIFICLY bad). What really makes innodb useless to me is that there's no real support for proper operation by MySQL itself. If you could force MySQL to only use innodb tables, and to NEVER do the wrong things syntactically, it would be ok. But there are thousands of foot-guns in the MySQL - Innodb combination waiting to take off your toes. Too many to count really. To me, that's what makes innodb so useless, the way MySQL fails to integrate well with it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Decide between Postgresql and Mysql (help of
This is as much about the code in front of the database as the database itself. You'll want to use an architecture that supports pooled connections (java, php under lighttpd, etc...) and you'll want to look Well, anybody who uses PHP and cares about performance is already using lighttpd, no ? MySQL and PostgreSQL can handle fairly heavy parallel loads. I'll only speak about MyISAM. MySQL == MyISAM. InnoDB is useless : if you want transactions, use postgres. If you say to yourself "oh yeah, but it would be cool to use a MyISAM table for stuff like hit counters etc"... Is it the job of a SQL database to count hits on the root page of your site ? No. To store user sessions ? No. The job of a SQL database is to efficiently handle data, not to do something that should stay in RAM in the application server process, or at worst, in a memcached record. MySQL + MyISAM has a huge advantage : it can look up data in the index without touching the tables. MySQL handles parallel SELECTs very well. However, throw in some maintenance operation which involves a long query with writes (like a big joined UPDATE) and all access to your website is blocked while the query lasts. This is worsened by the fact that MySQL sucks at complex queries. If all of your updates are done to a few rows, MyISAM is cool, but someday you'll want to do this query which locks a table during one minute... and then you got a problem. Just be very clear about what you want to do, what types of queries you'll want to run in two years... etc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Decide between Postgresql and Mysql (help of
Marcos wrote: So, what exactly are you planning on doing? The application will be a chat for web, the chats will be stored in the server. In a determined interval of time... more or less 2 seconds, the application will be looking for new messages. I believe that it will make many accesses. The write in disc will be constant. Ok. I would favor PostgreSQL for reasons of ease of future development. However, lets look at what both RDBMS's buy you: MySQL: 1) Possibility of many components for web apps that can be used though the lack of certain features (such as complex updateable views) makes this possibly an issue. 2) Great simple read performance. PostgreSQL: 1) Possibility to integrate any other components later (including those on MySQL via DBI-Link). 2) Fabulous community support (and I am sure fabulous paid support too given the fact that many of those who contribute to the great community support also offer paid support). 3) Better parallel write performance. 4) Greater extensibility, leading to greater flexibility down the road should you want to add in new components without rewriting your front-end. For a simple chat client, you can probably put something together with some Perl/CGI scripts, Jabber, and MySQL or PostgreSQL pretty easily and without much development labor at all. Indeed I would suggest that the RDBMS is, absent other specific concerns, the least of your issues. In other words, both are probably adequate. It is impossible to provide an estimate for capacity though without knowing the app in question, expected query composition, and so forth. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] simple join uses indexes, very slow
On Tue, 28 Mar 2006 19:17:49 +0100
Simon Riggs <[EMAIL PROTECTED]> threw this fish to the penguins:
> On Tue, 2006-03-28 at 10:22 -0500, george young wrote:
>
> > work_mem= 1024
>
> Set that higher.
>
> Try a couple of other plans using enable_* and let us have the EXPLAIN
> ANALYZE plans.
I tried this, but it doesn't seem to have made much difference that I can see:
newschm3=> show work_mem;
work_mem
--
8024
newschm3=> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM
run_opsets ro, parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num
and ro.run='team9';
QUERY PLAN
---
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=292.739..107672.525 rows=43050 loops=1)
-> Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=115.134..197.818 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=2.559..408.125 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND ("outer".opset_num =
p.opset_num))
-> Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=2.099..2.099 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND ("outer".opset_num =
p.opset_num))
Total runtime: 107860.493 ms
(8 rows)
newschm3=> shoe enable_nestloop;
ERROR: syntax error at or near "shoe" at character 1
LINE 1: shoe enable_nestloop;
^
newschm3=> show enable_nestloop;
enable_nestloop
-
on
(1 row)
newschm3=> set enable_nestloop=off;
SET
newschm3=> explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM
run_opsets ro, parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num
and ro.run='team9';
QUERY PLAN
---
Merge Join (cost=34177.87..34291.36 rows=6707 width=22) (actual
time=64654.744..64760.875 rows=43050 loops=1)
Merge Cond: ("outer".opset_num = "inner".opset_num)
-> Sort (cost=130.93..131.11 rows=71 width=18) (actual time=62.177..62.333
rows=263 loops=1)
Sort Key: ro.opset_num
-> Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=40.415..55.745 rows=263
loops=1)
Index Cond: (run = 'team9'::text)
-> Sort (cost=34046.94..34070.02 rows=9231 width=22) (actual
time=64592.526..64615.228 rows=43050 loops=1)
Sort Key: p.opset_num
-> Bitmap Heap Scan on parameters p (cost=272.31..33438.97 rows=9231
width=22) (actual time=333.975..64126.200 rows=43050 loops=1)
Recheck Cond: ('team9'::text = run)
-> Bitmap Index Scan on parameters_idx (cost=0.00..272.31
rows=9231 width=0) (actual time=309.199..309.199 rows=43050 loops=1)
Index Cond: ('team9'::text = run)
Total runtime: 64919.714 ms
(13 rows)
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Decide between Postgresql and Mysql (help of
So, what exactly are you planning on doing? The application will be a chat for web, the chats will be stored in the server. In a determined interval of time... more or less 2 seconds, the application will be looking for new messages. I believe that it will make many accesses. The write in disc will be constant. Ah, cool. That's exactly what a database is not designed for xD Try this, I coded this in about 1 hour as a joke last week. http://peufeu.com/demos/xhchat/ It works in firefox and opera, uses xmlhttprequest, and the messages are stored in a dbm database. We have also coded a real HTTP chat. I'll briefly expose the details on-list, but if you want the gory stuff, ask privately. There is a Postgres database for users, authentication, chatrooms and stuff. This database can be modified by a full-blown web application. Of course, messages are not stored in the database. It would be suicidal performance-wise to do so. An asynchronous HTTP server, using select() (lighttpd style) is coded in Python. It is very special-purpose server. It keeps an open connection with the client (browser) and sends messages as they arrive in the chatroom, with no delay. The connection is interrupted only when the client submits a new message via a form, but this is not mandatory. My memories are a bit old, but we benchmarked it at about 4000 messages/second on a low-end server (athlon something). Concurrent connections are unlimited. Disk I/O is zero. I like it. If you store messages in the database, you can hope to be about 10-50 times slower. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Decide between Postgresql and Mysql (help of
What really makes innodb useless to me is that there's no real support for proper operation by MySQL itself. If you could force MySQL to only use innodb tables, and to NEVER do the wrong things syntactically, it would be ok. But there are thousands of foot-guns in the MySQL That's what I meant actually. And by saying "if you want transactions" I also meant "if you want a database system that will go to great lengths to save your ass and your data instead of helping you shooting yourself in the foot, generally work very well, be reliable, friendly and a pleasure to work with, which means more or less, if you're rational rather than masochistic, then yeah, you should use postgres". If you could force MySQL to only use innodb tables, and to NEVER do the wrong things syntactically, it would be ok. You'd still need to teach it how to hash-join and everything, though. Life sucks when the only join type you have is merge join. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Decide between Postgresql and Mysql (help of
On Tue, Mar 28, 2006 at 09:42:51PM +0200, PFC wrote: > However, throw in some maintenance operation which involves a long > query with writes (like a big joined UPDATE) and all access to your > website is blocked while the query lasts. > This is worsened by the fact that MySQL sucks at complex queries. > > If all of your updates are done to a few rows, MyISAM is cool, but > someday you'll want to do this query which locks a table during one > minute... and then you got a problem. Not to mention that MyISAM loves to eat data. Livejournal suffered at least one major crash due to MyISAM corruption. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Decide between Postgresql and Mysql (help of
Heh, too quick on the send button... On Tue, Mar 28, 2006 at 09:42:51PM +0200, PFC wrote: > I'll only speak about MyISAM. MySQL == MyISAM. InnoDB is useless : > if you want transactions, use postgres. > If you say to yourself "oh yeah, but it would be cool to use a > MyISAM table for stuff like hit counters etc"... Is it the job of a > SQL > database to count hits on the root page of your site ? No. To store user > sessions ? No. The job of a SQL database is to efficiently handle data, > not to do something that should stay in RAM in the application server > process, or at worst, in a memcached record. Actually, it's entirely possible to do stuff like web counters, you just want to do it differently in PostgreSQL. Simply insert into a table every time you have a hit, and then roll that data up periodically. And using MyISAM is no panacea, either. Trying to keep a web counter in a MyISAM table means you'll serialize every web page on that counter update. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] MVCC intro and benefits docs?
On Tue, Mar 28, 2006 at 10:27:39PM +0200, Antoine wrote: > Hi, > Does anyone know of any fairly entry-level documentation for the > benefits-drawbacks of MVCC in the db? As it relates to performance? > Postgres vs the others? > Cheers > Antoine It's not dedicated to discussing MVCC alone, but http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2 might provide you with some useful info. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] simple join uses indexes, very slow
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Steinar H. Gunderson > A merge join requires sorted inputs. > > > Most of the time was spent sorting the parameters parameters table by > > opset_num even though opset_num is indexed. Isn't Postgres able to walk > the > > index instead of sorting? > > The time of an index scan vs. a sequential scan + sort depends on several > factors, so it's not just a matter of walking the index whenever there is > one. I was just looking this over again and I realized I misread the query plan. The slowest step was the Bitmap Heap Scan not the sort. (The sort was relatively fast.) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] MVCC intro and benefits docs?
""Jim C. Nasby"" <[EMAIL PROTECTED]> wrote > > It's not dedicated to discussing MVCC alone, but > http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2 > might provide you with some useful info. > -- Another introduction is here: http://www.postgresql.org/files/developer/transactions.pdf Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] simple join uses indexes, very slow
george young wrote:
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I have a simple join on two tables that takes way too long. Can you help
me understand what's wrong? There are indexes defined on the relevant columns.
I just did a fresh vacuum --full --analyze on the two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745 tuples.
explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM run_opsets ro,
parameters p WHERE ro.run = p.run AND ro.opset_num = p.opset_num and
ro.run='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
-> Index Scan using run_opsets_pkey on run_opsets ro (cost=0.00..128.75
rows=71 width=18) (actual time=0.386..62.959 rows=263 loops=1)
Index Cond: (run = 'team9'::text)
-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27 width=22)
(actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND ("outer".opset_num =
p.opset_num))
-> Bitmap Index Scan on parameters_idx (cost=0.00..2.16 rows=27
width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND ("outer".opset_num =
p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table "public.run_opsets"
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
"run_opsets_pkey" PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table "public.parameters"
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
"parameters_idx" btree (run, opset_num, step_num, opset, opset_ver, step,
step_ver, name, split, wafers)
"parameters_opset_idx" btree (opset, step, name)
"parameters_step_idx" btree (step, name)
More for my own information (because nobody else has suggested it),
would it make a difference if 'run' was a varchar field rather than text?
--
Postgresql & php tutorials
http://www.designmagick.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] simple join uses indexes, very slow
If your looking for suggestions, I would suggest updating the 8.1.x you
have installed to the latest version, as of typing this is 8.1.3 ;) Most
notable is some of the -bug- fixes that are in since 8.1.0, for example;
* Fix incorrect optimizations of outer-join conditions (Tom)
You know, minor point releases aren't adding new features or changing
basic functionality, they are pure and simple bugfixes. If I was in
-your- position, I would run (don't walk ;) and install upto 8.1.3
of course, thats jst my 2c, feel free to ignore :D
Regards
Stef
Chris wrote:
george young wrote:
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I have a simple join on two tables that takes way too long. Can you
help
me understand what's wrong? There are indexes defined on the
relevant columns.
I just did a fresh vacuum --full --analyze on the two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745
tuples.
explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM
run_opsets ro, parameters p WHERE ro.run = p.run AND ro.opset_num =
p.opset_num and ro.run='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
-> Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959
rows=263 loops=1)
Index Cond: (run = 'team9'::text)
-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
width=22) (actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND
("outer".opset_num = p.opset_num))
-> Bitmap Index Scan on parameters_idx (cost=0.00..2.16
rows=27 width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND
("outer".opset_num = p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table "public.run_opsets"
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
"run_opsets_pkey" PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table "public.parameters"
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
"parameters_idx" btree (run, opset_num, step_num, opset,
opset_ver, step, step_ver, name, split, wafers)
"parameters_opset_idx" btree (opset, step, name)
"parameters_step_idx" btree (step, name)
More for my own information (because nobody else has suggested it),
would it make a difference if 'run' was a varchar field rather than text?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Slow performance on Windows .NET and OleDb
The query is, select * from users which returns 4 varchar fields, there is no where clause Yes, I am running the default postgres config. Basically I have been a MySQL user and thought I would like to check out PostGreSql. So I did a quick performance test. The performance was so different that I thought PostGreSQL was nothing compared to MySQL, but now it seems its just a few configuration options. Strange how the defult config would be so slow... I have begun reading the documentation but am not too sure what options I can quickly tweak to get good performance, could somebody give me some tips? Thanks - Original Message - From: "Joshua D. Drake" <[EMAIL PROTECTED]> To: "Jim C. Nasby" <[EMAIL PROTECTED]> Cc: "Greg Quinn" <[EMAIL PROTECTED]>; Sent: Tuesday, March 28, 2006 7:52 PM Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb Jim C. Nasby wrote: On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote: Hello, I have just installed PostGreSql 8.1 on my Windows XP PC. I created a simple table called users with 4 varchar fields. I am using the OleDb connection driver. In my .NET application, I populate 3000 records into the table to test PostGreSql's speed. It takes about 3-4 seconds. Even worse is displaying the 3000 records in a ListView control. It takes about 7 seconds. In MySQL, the exact same table and application displays the same 3000 records in under 1/2 second!!! Have you vacuumed recently? This smells like it might be a table bloat problem. This could be a lot of things... He is probably running the default postgresql.conf which is going to perform horribly. What is your work_mem? shared_buffers? Are you passing a where clause? If so is there an index on the field that is subject to the clause? When you do the population, is it via inserts or copy? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Slow performance on Windows .NET and OleDb
Via insert When you do the population, is it via inserts or copy? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Slow performance on Windows .NET and OleDb
Greg Quinn wrote: The query is, select * from users which returns 4 varchar fields, there is no where clause Yes, I am running the default postgres config. Basically I have been a MySQL user and thought I would like to check out PostGreSql. So I did a quick performance test. The performance was so different that I thought PostGreSQL was nothing compared to MySQL, but now it seems its just a few configuration options. Strange how the defult config would be so slow... My english is poor but im gonna try to explain it: Default configuration in postgres its not for good performance, its just design to make it working in any computer. Thats why u have to try to custom default config file. Anyway, people says that mysql is faster (and lighter) than postgres (at least with mysql 3.x vs postgres 7.4), but postgres is more advanced and its much harder to get data corrupted. But there is something that you should known about postgres. Postgres creates statistics of usage, and when you "vacumm", it optimizes each table depending of usage. So: - You should custom config file. - You should vacumm it, as someone recomended before. - Do u have any indexes? Remove it. To get all rows you do not need it Note that I just have use it under Linux, i have no idea about how should it work on Windows. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow performance on Windows .NET and OleDb
Ruben Rubio Rey wrote: Greg Quinn wrote: The query is, select * from users which returns 4 varchar fields, there is no where clause Yes, I am running the default postgres config. Basically I have been a MySQL user and thought I would like to check out PostGreSql. So I did a quick performance test. The performance was so different that I thought PostGreSQL was nothing compared to MySQL, but now it seems its just a few configuration options. Strange how the defult config would be so slow... - Do u have any indexes? Remove it. To get all rows you do not need it I wouldn't do that. Postgres needs indexing just like any other database. It might affect this query but it's not going to help other queries. -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
