Re: [PERFORM] Horribly slow query/ sequential scan

ubject: Re: [PERFORM] Horribly slow query/ sequential scan Yes it does: SET EXPLAIN ON; It writes the file to sqexplain.out -----Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, January 09, 2007 9:13 AM To: Gregory S. Williamson Cc: [EMA

Re: [PERFORM] Horribly slow query/ sequential scan

ailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 4:50 AM To: [EMAIL PROTECTED]; Gregory S. Williamson Cc: pgsql-performance@postgresql.org Subject:AW: [PERFORM] Horribly slow query/ sequential scan Forget abount "IN". Its horribly slow. try : select w.appid, w.rate,

Re: [PERFORM] Horribly slow query/ sequential scan

((w.client_id)::text = '227410'::text)) Total runtime: 6.110 ms (16 rows) Thanks again (and sorry for the top-posting but this particular interface is ungainly) G -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 4:35 AM To: Gre

[PERFORM] Horribly slow query/ sequential scan

This is a query migrated from postgres. In postgres it runs about 10,000 times *slower* than on informix on somewhat newer hardware. The problem is entirely due to the planner. This PostgreSQL 8.1.4 on linux, 2 gigs of ram. The table: Table "reporting.bill_rpt_work" Column |

Re: [PERFORM] New to PostgreSQL, performance considerations

(Re)-Design it to do both, unless there's reason to believe that doing one after the other would skew the results. Then old results are available, new results are also visible and useful for future comparisons. And seeing them side by side mught be an interesting exercise as well, at least for

Re: [PERFORM] Hardware advice

If your data is valuable I'd recommend against RAID5 ... see performance aside, I'd advise against RAID5 in almost all circumstances. Why take chances ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTE

Re: [PERFORM] Slow SELECT on three or more clients

Operating system and some of the basic PostreSQL config settings would be helpful, plus any info you have on your disks, the size of the relevant tables, their structure and indexes & vacuum/analyze status ... plus what others have said: Upgrade! There are considerable improvements in, well, *

Re: [PERFORM] Context switch storm

Based on what other people have posted, hyperthreading seems not to be beneficial for postgres -- try searching through the archives of this list. (And then turn it off and see if it helps.) You might also post a few details: config settings (shared_buffers, work_mem, maintenance_work_mem, wal

Re: [PERFORM] Optimization of this SQL sentence

Off hanbd I can't recommend anything, bur perhaps you could post the details of the tables (columns, indexes),and some info on what version of postgres you are using. Are the tables recently analyzed ? How many rows in them ? Greg Williamson DBA GlobeXplorer LLC -Original Message- Fro

Re: [PERFORM] Is postgresql ca do the job for software deployed in

A sodden late night idea ... schemas don't need to have names that are meaningful to outsiders. Still, the point about "political" aspects is an important one. OTH, schemas provide an elegant way of segregating data. My $0.02 (not worth what it was) Greg Williamson DBA GlobeXplorer LLC -O

Re: [PERFORM] Benchmarck PostgreSQL 8.1.4 MySQL 5.0.20 and Oracle 10g2

That fsync off would make me very unhappy in a production environment not that turning it on would help postgres, but ... one advantage of postgres is its reliability under a "pull the plug" scenario, but this setting defeats that. FWIW, Xeon has gotten quite negative reviews in these quar

Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal

1.451 ms = 1.451 milliseconds 1451.0 ms = 1.451 seconds ... so 32.918 ms for a commit seems perhaps reasonable ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Zeugswetter Andreas DCP SD Sent: Thu 5/11/2006 12:55 AM To: Jim C. Nasby

Re: [PERFORM] [postgis-users] Is my query planner failing me, or vice versa?

obviously I am but a humble user. grin. G -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wed 12/14/2005 9:36 PM To: Gregory S. Williamson Cc: pgsql-performance@postgresql.org; PostGIS Users Discussion Subject:Re: [PERFORM] [postgis-users] Is my quer

Re: [PERFORM] [postgis-users] Is my query planner failing me, or vice versa?

Forgive the cross-posting, but I found myself wondering if might not be some way future way of telling the planner that a given table (column ?) has a high likelyhood of being TOASTed. Similar to the random_page_cost in spirit. We've got a lot of indexed data that is spatial and have some table

Re: [PERFORM] choosing RAID level for xlogs

I would be very cautious about ever using RAID5, despite manufacturers' claims to the contrary. The link below is authored by a very knowledgable fellow whose posts I know (and trust) from Informix land. Greg Williamson DBA GlobeXplorer L

Re: [PERFORM] Question

As a sometimes Informix and PostgreSQL DBA, I disagree with the contentions below. We have many tables with 10s of millions of rows in Postgres. We have had (alas) power issues with our lab on more than one occasion and the afflicted servers have recovered like a champ, every time. This person

Re: [PERFORM] Low Performance for big hospital server ..

Amrit -- >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: Mon 1/3/2005 12:18 AM >To:Mark Kirkwood >Cc:PGsql-performance >Subject: Re: [PERFORM] Low Performance for big hospital server .. >> shared_buffers = 12000 will use 12000*8192 bytes (i.e a

Re: [PERFORM] Improve BULK insertion

Rodrigo -- You should definitely drop the indexes and any other FK constraints before loading and then rebuild them. Check your logs and see if there are warnings about checkpoint intervals -- only 3 logs seems like it might be small; if you have the disk space I would definitely consider raisi

Re: [PERFORM] Performance vs Schemas

Igor, I'm not sure if it is proper to state that schemas are themselves speeding things up. As an example, we have data that is usually accessed by county; when we put all of the data into one big table and select from it using a code for a county of interest, the process is fairly slow as ther

Re: [PERFORM] Cleaning up indexes

If you have set up the postgres instance to write stats, the tables pg_stat_user_indexes, pg_statio_all_indexes and so (use the \dS option at the psql prompt to see these system tables); also check the pg_stat_user_tables table and similar beasts for information on total access, etc. Between th

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

FWIW, Informix does allow the fragmentation of data over named dbspaces by round-robin and expression; this is autosupporting as long as the dba keeps enough space available. You may also fragment the index although there are some variations depending on type of Informix (XPS, etc.); this is av

Re: [PERFORM] postgresql performance with multimedia

Not sure about the overall performance, etc. but I think that in order to collect statistics you need to set some values in the postgresql.conf config file, to wit: #--- # RUNTIME STATISTICS #-

Re: [PERFORM] Bulk Insert and Index use

If it has to read a majority (or even a good percentage) of the rows in question a sequential scan is probably faster ... and as Jim pointed out, a temp table can often be a useful medium for getting speed in a load and then allowing you to clean/alter data for a final (easy) push. G -Origi

Re: [PERFORM] Bulk Insert and Index use

Usualy any bulk load is faster with indexes dropped and the rebuilt ... failing that (like you really need the indexes while loading, say into a "hot" table) be sure to wrap all the SQL into one transaction (BEGIN;...COMMIT;) ... if any data failes it all fails, which is usually easier to deal

Re: [PERFORM] General performance questions about postgres on Apple

Why is my name on a mail from Tom Lane ? Really, he knows a *lot* more than I and should get due credit. Seriously, is this the peformance remailer mangling something ? Greg Williamson (the real one) -Original Message- From: Gregory S. Williamson Sent: Sun 6/6/2004 10:46 PM To

Re: [PERFORM] count(*) slow on large tables

I can tell you that this is one of the first thing applications' programmers and IT managers notice. It can slightly tarnish postgres' image when it takes it many long seconds to do what other databases can do in a snap. The "whys and wherefores" can be hard to get across once they see the comp

Re: [PERFORM] Hardware recommendations to scale to silly load

Nitpicking -- Perhaps the 4th data line is meant to be: Inserts in separate transactions 2500 inserts/second ^^^ ?? Greg Williamson -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tue 9/9/2003 8:25 PM To:

Re: [PERFORM] Perfomance Tuning

FWIW, Informix can be run using a "cooked" (Unix) file for storing data or it uses "raw" disk space and bypasses the ordinary (high level) UNIX controllers and does its own reads/writes. About 10 times faster and safer. Of course, itmay have taken a lot of programmer time to make that solid. But