Re: [PERFORM] Performance problems on a fairly big table with two key columns.
On Thursday 04 September 2003 23:53, Rasmus Aveskogh wrote: Hi, I have a table that looks like this: DATA ID TIME |--||--| The table holds app. 14M rows now and grows by app. 350k rows a day. The ID-column holds about 1500 unique values (integer). The TIME-columns is of type timestamp without timezone. I have one index (b-tree) on the ID-column and one index (b-tree) on the time-column. My queries most often look like this: SELECT DATA FROM tbl WHERE ID = 1 AND TIME now() - '1 day'::interval; [snip] I tried applying a multicolumn index on ID and TIME, but that one won't even be used (after ANALYZE). The problem is likely to be that the parser isn't spotting that now()-'1 day' is constant. Try an explicit time and see if the index is used. If so, you can write a wrapper function for your expression (mark it STABLE so the planner knows it won't change during the statement). Alternatively, you can do the calculation in the application and use an explicit time. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] SELECT's take a long time compared to other DBMS
Relaxin, I can't remember during this thread if you said you were using ODBC or not. If you are, then your problem is with the ODBC driver. You will need to check the Declare/Fetch box or you will definitely bring back the entire recordset. For small a small recordset this is not a problem, but the larger the recordset the slower the data is return to the client. I played around with the cache size on the driver and found a value between 100 to 200 provided good results. HTH Patrick Hatcher Relaxin [EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: gresql.org Subject: Re: [PERFORM] SELECT's take a long time compared to other DBMS 09/04/2003 07:13 PM Thank you Christopher. Change fsync to true (you want your data to survive, right?) and increase shared buffers to something that represents ~10% of your system memory, in blocks of 8K. I turned it off just in the hope that things would run faster. None of this is likely to substantially change the result of that one query, however, and it seems quite likely that it is because PostgreSQL is honestly returning the whole result set of ~100K rows at once, whereas the other DBMSes are probably using cursors to return only the few rows of the result that you actually looked at. Finally, someone who will actually assume/admit that it is returning the entire result set to the client. Where as other DBMS manage the records at the server. I hope PG could fix/enhance this issue. There are several issues that's stopping our company from going with PG (with paid support, if available), but this seems to big the one at the top of the list. The next one is the handling of BLOBS. PG handles them like no other system I have ever come across. After that is a native Windows port, but we would deal cygwin (for a very little while) if these other issues were handled. Thanks Christopher Browne [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] A long time ago, in a galaxy far, far away, Relaxin [EMAIL PROTECTED] wrote: Have you changed any of the settings yet in postgresql.conf, specifically the shared_buffers setting? fsync = false tcpip_socket = true shared_buffers = 128 Change fsync to true (you want your data to survive, right?) and increase shared buffers to something that represents ~10% of your system memory, in blocks of 8K. So, if you have 512MB of RAM, then the total blocks is 65536, and it would likely be reasonable to increase shared_buffers to 1/10 of that, or about 6500. What is the value of effective_cache_size? That should probably be increased a whole lot, too. If you are mainly just running the database on your system, then it would be reasonable to set it to most of memory, or (* 1/2 (/ (* 512 1024 1024) 8192)) 32768. None of this is likely to substantially change the result of that one query, however, and it seems quite likely that it is because PostgreSQL is honestly returning the whole result set of ~100K rows at once, whereas the other DBMSes are probably using cursors to return only the few rows of the result that you actually looked at. -- cbbrowne,@,cbbrowne.com http://www3.sympatico.ca/cbbrowne/linuxdistributions.html Rules of the Evil Overlord #14. The hero is not entitled to a last kiss, a last cigarette, or any other form of last request. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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] Performance problems on a fairly big table with two
Richard, Thanks a lot! You were right - the query parser misunderstood now() - '1 day'::interval and only used one of the indexes (as I already noticed). Actually all I had to do was to cast the result like this: (now() - '1 day'::interval)::date 75s is not between 10ms and 200ms. Thanks again! -ra On Thursday 04 September 2003 23:53, Rasmus Aveskogh wrote: Hi, I have a table that looks like this: DATA ID TIME |--||--| The table holds app. 14M rows now and grows by app. 350k rows a day. The ID-column holds about 1500 unique values (integer). The TIME-columns is of type timestamp without timezone. I have one index (b-tree) on the ID-column and one index (b-tree) on the time-column. My queries most often look like this: SELECT DATA FROM tbl WHERE ID = 1 AND TIME now() - '1 day'::interval; [snip] I tried applying a multicolumn index on ID and TIME, but that one won't even be used (after ANALYZE). The problem is likely to be that the parser isn't spotting that now()-'1 day' is constant. Try an explicit time and see if the index is used. If so, you can write a wrapper function for your expression (mark it STABLE so the planner knows it won't change during the statement). Alternatively, you can do the calculation in the application and use an explicit time. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [GENERAL] Seq scan of table?
I think I have found out why.. I have a where clause on a ID field but it seems like I need to cast this integer to the same integer as the field is defined in the table, else it will do a tablescan. Is this assumtion correct? And if it is, do I then need to change all my sql's to cast the where clause where I just have a number (eg where field = 1) to force the planner to use index scan instead of seq scan? BTJ I am trying to tune my database and I discovered one select that does a seq scan on a table but I can't see why... All the join fields are indexed and I am returning just one record, so no sort is done. Does it just pick seq scan for the heck of it or is it a reason? Regards, BTJ --- Bjørn T Johansen (BSc,MNIF) Executive Manager [EMAIL PROTECTED] Havleik Consulting Phone : +47 67 54 15 17 Conradisvei 4 Fax : +47 67 54 13 91 N-1338 Sandvika Cellular : +47 926 93 298 http://www.havleik.no --- The stickers on the side of the box said Supported Platforms: Windows 98, Windows NT 4.0, Windows 2000 or better, so clearly Linux was a supported platform. --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [GENERAL] Seq scan of table?
On Friday 05 September 2003 09:47, Bjorn T Johansen wrote: I think I have found out why.. I have a where clause on a ID field but it seems like I need to cast this integer to the same integer as the field is defined in the table, else it will do a tablescan. Is this assumtion correct? And if it is, do I then need to change all my sql's to cast the where clause where I just have a number (eg where field = 1) to force the planner to use index scan instead of seq scan? PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [GENERAL] Seq scan of table?
On Fri, 2003-09-05 at 12:07, Richard Huxton wrote: On Friday 05 September 2003 09:47, Bjorn T Johansen wrote: I think I have found out why.. I have a where clause on a ID field but it seems like I need to cast this integer to the same integer as the field is defined in the table, else it will do a tablescan. Is this assumtion correct? And if it is, do I then need to change all my sql's to cast the where clause where I just have a number (eg where field = 1) to force the planner to use index scan instead of seq scan? PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. Oki, I am using both int2 and int8 as well, so that explains it... Thanks! BTJ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [GENERAL] Seq scan of table?
I think I have found out why.. I have a where clause on a ID field but it seems like I need to cast this integer to the same integer as the field is defined in the table, else it will do a tablescan. Yes, this is correct Is this assumtion correct? And if it is, do I then need to change all my sql's to cast the where clause where I just have a number (eg where field = 1) to force the planner to use index scan instead of seq scan? Someone correct me if I'm wrong, but I believe numbers are int4's, so they need to be cast if your column is not an int4. Jon BTJ I am trying to tune my database and I discovered one select that does a seq scan on a table but I can't see why... All the join fields are indexed and I am returning just one record, so no sort is done. Does it just pick seq scan for the heck of it or is it a reason? Regards, BTJ --- Bjørn T Johansen (BSc,MNIF) Executive Manager [EMAIL PROTECTED] Havleik Consulting Phone : +47 67 54 15 17 Conradisvei 4 Fax : +47 67 54 13 91 N-1338 Sandvika Cellular : +47 926 93 298 http://www.havleik.no --- The stickers on the side of the box said Supported Platforms: Windows 98, Windows NT 4.0, Windows 2000 or better, so clearly Linux was a supported platform. --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [GENERAL] Seq scan of table?
On Friday 05 September 2003 19:20, Neil Conway wrote: On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. Or enclose the integer literal in single quotes. You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. Well, it's definitely a bug in PG, it's quite flexible type structure notwithstanding. It certainly catches out a lot of people. I'd guess it's in the top three issues in the general/sql lists. I'd guess part of the problem is it's so silent. In some ways it would be better to issue a NOTICE every time a typecast is forced in a comparison - irritating as that would be. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [GENERAL] Seq scan of table?
On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. Or enclose the integer literal in single quotes. You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. Well, it's definitely a bug in PG, it's quite flexible type structure notwithstanding. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [GENERAL] Seq scan of table?
Neil Conway [EMAIL PROTECTED] writes: On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: You should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. Well, it's definitely a bug in PG, it's quite flexible type structure notwithstanding. Let's say it's something we'd really like to fix ;-) ... and will, as soon as we can figure out a cure that's not worse than the disease. Dorking around with the semantics of numeric expressions has proven to be a risky business. See, eg, the thread starting here: http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
