[GENERAL] Weird problem concerning tsearch functions built into postgres 8.3, assistance requested
I've been tasked with maintaining a set of postgres databases created by my predecessor in this position. The original databases several years back were version 8.1.3, and used the tsearch2 functions to enable some client-program searches. We've recently begun preparing to shift to 8.3 (I believe the current starter box we're putting together for cloning is running 8.3.1), and I've been having a bit of trouble with the built-in searching. I think I've got it mostly fixed after following a few leads online, but I've run across one last little bit of problem that I can't figure out how to get around, and that doesn't make much sense to me. Admittedly, I don't know very much about tsearch. One of the tables we're using in the 8.1.3 setups currently running includes phone numbers as a searchable field (fti_phone), with the results of a select on the field generally looking like this: 'MMM':2 '':3 'MMM-':1. MMM is the first three digits, is the fourth-seventh. The weird part is this: On the old systems running 8.1.3, I can look up a record by fti_phone using any of the three above items; first three, last four, or entire number including dash. On the new system running 8.3.1, I can do a lookup by the first three or the last four and get the results I'm after, but any attempt to do a lookup by the entire MMM- version returns no records. I saw nothing concerning this while I was looking for information on how to get the search functions properly working in postgres 8.3.1, nor have I specifically seen anything since running across that problem. The latter, however, may simply be because I don't know how to properly phrase my searches. Does anyone have any information they would be willing to share regarding this issue, or a link to a website which discusses it? I would greatly appreciate any advice I may be given.
[GENERAL] Stripping apostrophes from data
Is there some program or procedure for stripping apostrophes (') from data in
the db? Most of our data has been shuffled over to Postgres from an older
system, and I'm occasionally running into data entered in the old system that
has apostrophes in it. (Most recent example: A name field with the word
Today's in it.) Given that most of my interactions with the database are
through perl scripts and php pages, I can't always tell ahead of time what
field I need is going to contain data that's deadly to my statements.
Alternately, is there some way of inserting or selecting data from the db
which doesn't require the use of apostrophes for non-numeric fields?
-
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.
Re: [GENERAL] Stripping apostrophes from data
The dollar quoting appears to have fixed it; thank you. I apologize for my
folly in sending out the original message.
Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 20, 2007, at 11:19 , Andrew Edson wrote:
Is there some program or procedure for stripping apostrophes (')
from data in the db? Most of our data has been shuffled over to
Postgres from an older system, and I'm occasionally running into
data entered in the old system that has apostrophes in it. (Most
recent example: A name field with the word Today's in it.)
Do you want to remove the double quotes around the word or the
apostrophe between y and s? Regardless, you might want to look at the
regexp_replace or translate functions:
http://www.postgresql.org/docs/8.2/interactive/functions-string.html
Given that most of my interactions with the database are through
perl scripts and php pages, I can't always tell ahead of time what
field I need is going to contain data that's deadly to my statements.
Sounds like a problem with how you're handling your data in your
middleware, as this shouldn't be a problem regardless of the
characters in the string if you're handling things correctly. If you
post an example perhaps people can offer suggestions on how you can
handle things more safely. Are you interpolating variables directly
into SQL statements? If so, don't do that: use bind variables instead.
Alternately, is there some way of inserting or selecting data from
the db which doesn't require the use of apostrophes for non-numeric
fields?
You could use dollar quotes, but it sounds like your problem might be
able to be solved using bind variables.
Michael Glaesemann
grzm seespotcode net
-
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
Re: [GENERAL] Select time jump after adding filter; please help me figure out what I'm doing wrong.
I apologize about the CC; I thought I had done so. There are fourteen (14) distinct values in rcrd_cd. And I don't know if this counts as something odd, but I got the following values by doing a vacuum full analyze, then running the set with index, dropping index, running set without. Values with index: attest=# select count(*) from ptrans; 16488578 attest=# select count(*) from ptrans where rcrd_cd = '0A'; 6701655 attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = ' 0A'; Bitmap Heap Scan on ptrans (cost=1223.86..151183.39 rows=87439 width=21) (actu al time=2255.640..70371.304 rows=6701655 loops=1) - Bitmap Index Scan on ptrans_cid_trandt_idx (cost=0.00..1223.86 rows=8743 9 width=0) (actual time=2216.856..2216.856 rows=204855 loops=1) Total runtime: 89964.419 ms Values without index: attest=# select count(*) from ptrans; 16488578 attest=# select count(*) from ptrans where rcrd_cd = '0A'; 204855 attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A'; Seq Scan on ptrans (cost=0.00..384813.22 rows=87439 width=21) (actual time=20.286..65330.049 rows=204855 loops=1) Filter: (rcrd_cd = '0A'::bpchar) Total runtime: 65945.160 ms Again, someone mind pointing out to me where I've managed to mess this one up? Richard Huxton [EMAIL PROTECTED] wrote: Don't forget to CC: the list. Andrew Edson wrote: I apologize; You are correct in that I mistyped my original structure. Here is the information for the correct explain and explain analyze statements. attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A'; No need for the simple explain - explain analyse includes all the information. attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = '0A'; QUERY PLAN Bitmap Heap Scan on ptrans (cost=1223.86..149853.23 rows=85741 width=21) (actual time=2302.363..70321.838 rows=6701655 loops=1) - Bitmap Index Scan on ptrans_cid_trandt_idx (cost=0.00..1223.86 rows=85741 width=0) (actual time=2269.064..2269.064 rows=204855 loops=1) Total runtime: 89854.843 ms Well, it's taking 90 seconds to return 6.7 million rows. Depending on your system and memory settings, that might not be unreasonable. It *is* getting the estimate of returned rows wrong (it thinks 85,741 will match) which is hugely out of line. Is there something odd with this table/column or haven't you analysed recently? How many unique values does rcrd_cd have, and how many rows does the table have? I don't know that you'll get this down to sub-second responses though, not if you're trying to return 6 million rows from an even larger table. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
Re: [GENERAL] Select time jump after adding filter; please help me figure out what I'm doing wrong.
Yes. The only difference between the two selects was that the index on the table in question was dropped. As far as I know, that was the only partial index on there, although since it's a test db, I could probably go in and experiment on a few more if needed. This problem may have already been solved; I'm using an older version of Postgres; 8.1.3. My boss has requested that it not be upgraded just yet, however, so I'm stuck with it for the moment. Richard Huxton [EMAIL PROTECTED] wrote: Andrew Edson wrote: I apologize about the CC; I thought I had done so. no problem There are fourteen (14) distinct values in rcrd_cd. And I don't know if this counts as something odd, but I got the following values by doing a vacuum full analyze, then running the set with index, dropping index, running set without. Might want to do ALTER TABLE ... ALTER COLUMN rcrd_cd SET STATISTICS = 14 (or a few more than that if you think it might be useful) - won't help you with this though. So - are you saying that with these two queries... attest=# select count(*) from ptrans where rcrd_cd = '0A'; 6701655 attest=# select count(*) from ptrans where rcrd_cd = '0A'; 204855 ...the only difference is that you've dropped an index? Because that's just strange - and I don't think it's anything you're doing. Do you have other partial indexes for different values of rcrd_cd, and do they have similar problems? If this can be reproduced it might point to something odd with bitmap scans. Oh, remind me what version of PostgreSQL you're running? -- Richard Huxton Archonet Ltd - Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
Re: [GENERAL] Select time jump after adding filter; please help me figure out what I'm doing wrong.
Tom Lane [EMAIL PROTECTED] wrote: Ah. I think your result is explained by this 8.1.4 bug fix: 2006-05-18 14:57 tgl * src/backend/optimizer/plan/createplan.c (REL8_1_STABLE): When a bitmap indexscan is using a partial index, it is necessary to include the partial index predicate in the scan's recheck condition. Otherwise, if the scan becomes lossy for lack of bitmap memory, we would fail to enforce that returned rows satisfy the predicate. Noted while studying bug #2441 from Arjen van der Meijden. IOW, once the bitmap goes lossy, we'd return *every* row on any page that the index fingered as having *any* relevant row. Better press him a little harder. There are a lot of known bugs in 8.1.3, and not any very good reason not to update to a later 8.1.x. As a really short-term measure, you could possibly avoid this bug by increasing work_mem enough that the bitmap doesn't get compressed. regards, tom lane I'll press as I can...thanks for pointing that out to me. How would I determine how much to increase work_mem by (or to) until then? - Got a little couch potato? Check out fun summer activities for kids.
[GENERAL] Select time jump after adding filter; please help me figure out what I'm doing wrong.
I've been working on a db project intended to perform modifications to one db based on the data stored in another one. Specifically, I'm supposed to check a pair of tables based on two criteria; an id field, and a timestamp. This will be crossreferenced with the second database; the timestamps will be used to update timestamps on anything which resides in the table of interest there. I was running the sequence through perl; with 76 records in the test copy of the second database, I was getting a four, five minute run. Not really bad, but I'm expecting the number of records in the second db to hit several thousand in production, so I thought I'd see if I could speed things up by adding an index on what I'm searching for. After about an hour of run time, I killed the program and started looking into things. The following is a copy of my index creation statement, index name, and explain and explain analyze output on the statement I was trying to run. Would someone please help me figure out what I'm doing wrong here? attest=# create index ptrans_cid_trandt_idx on ptrans(cntrct_id, tran_dt) where rcrd_cd = '0A'; ptrans_cid_trandt_idx btree (cntrct_id, tran_dt) WHERE rcrd_cd = '0A'::bpchar attest=# explain select cntrct_id, tran_dt from ptrans where rcrd_cd = 0; QUERY PLAN Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21) Filter: ((rcrd_cd)::text = '0'::text) (2 rows) attest=# explain analyze select cntrct_id, tran_dt from ptrans where rcrd_cd = 0; QUERY PLAN -- Seq Scan on ptrans (cost=0.00..426034.67 rows=82443 width=21) (actual time=60585.740..60585.740 rows=0 loops=1) Filter: ((rcrd_cd)::text = '0'::text) Total runtime: 60585.797 ms (3 rows) - Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
[GENERAL] Error restarting postmaster
Yesterday, one of the (replicated) remote databases I work with somehow got corrupted, so I attempted to drop a new copy off of the master (on a different box) and rebuild the database. Creation, language install, schema reload, all appeared to go well. On the actual data reload, I set the system aside and went on to something else, as the db takes a while to load. I came back to discover that the connection between my system and the one where the db was being rebuilt had been severed. Opening a new remote connection, I went in and attempted to rebuild the database, only to discover that neither postgres nor the postmaster was running. And when I attempted to restart the postmaster process, I received the following error: [EMAIL PROTECTED]:/usr/local/pgsql/bin ./postmaster -D /usr/local/pgsql/data/ LOG: database system was interrupted while in recovery at 2007-07-31 08:17:22 CDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. LOG: checkpoint record is at 3C/D7008078 LOG: redo record is at 3C/D7008078; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 59170527; next OID: 532878 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 3C/D70080BC PANIC: block 39 unfound WARNING: autovacuum not started because of misconfiguration HINT: Enable options stats_start_collector and stats_row_level. LOG: startup process (PID 6403) was terminated by signal 6 LOG: aborting startup due to startup process failure A google search on the Panic clause lead me to an old thread in the [Hackers] list, which looks like it was a similar problem, but I can't figure out from that thread how the problem was solved. Would someone please help me figure out what I need to do to correct this and get my database running again? - Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.
Re: [GENERAL] Error restarting postmaster
Is somewhat old, 8.1.3. I'll try to upgrade it to the 8.1.9. The box is running on SuSE 9.2, if I recall correctly...which binary rpm should I snag for that? Tom Lane [EMAIL PROTECTED] wrote: Andrew Edson writes: PANIC: block 39 unfound LOG: startup process (PID 6403) was terminated by signal 6 LOG: aborting startup due to startup process failure What PG version is this? (If your answer had a release date more than about a year ago, first update to the latest release in that branch and see if that fixes it.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
[GENERAL] Panic error on attempted update
One of the servers I am responsible for maintaining was apparently having problems earlier today. The servers are all running SuSE 9.2, Apache 2 (not sure what version), and Postgres 8.1.4. Our main server is running Slony 1.1, I think, creating Log-Shipping records that the rest of the servers are then updated off of by means of an hourly cron script. When I went into the server to take a look at the cause of the problems, I noticed that there were numerous copies of the updating cron script running, all of them trying to do a select on that server's local database. (There was a VACUUM command running at the same time; I do not know whether that had anything to do with this or not.) I disabled the script so it wouldn't be able to run again and create more overhead to deal with, then attempted to let the backlogged copies finish. They have since dissappeared, but attempting to run the script manually produces the following error messages: slony1_log_1_03476171.sql ERROR: Slony-I: set 11 is on sync 3475773, this archive log expects 3476170 ERROR: current transaction is aborted, commands ignored until end of transaction block ... ... ERROR: current transaction is aborted, commands ignored until end of transaction block PANIC: right sibling's left-link doesn't match server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost Any clue what's causing the panic message to occur? I understand the aborted transactions, since I'm giving it archive logs later than the one it expects, but I have no clue why I'm getting the Panic call, nor do I understand what it means completely. Would someone please explain to me? - Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online.
[GENERAL] Disappearing table - suggestions on possible causes appreciated.
The company I work for provides services for various offices around the country. In order to help keep our database straight, and allow several of our client-side programs to verify their location, we include a table called 'region' in our database. Although the database is replicated by Slony, this particular table I do not think is. Servers running the db are SuSE 9.3, running Postgresql 8.1.3, and I believe Slony 1.1. Recently, there have been incidents across a few of the offices where the region table in their local copy of the database has mysteriously lost all of its data. When this happens, the programs that require it (most of our client-side stuff) will not retrieve data related to those areas. I've already built an sql script to restore the table quickly, I'm just curious as to why this is happening. It's not really a common event, as it's only happened maybe five times or so across a few dozen servers and about three months. The most recent incident was brought to our attention today, which is why this is on my mind now. Does anyone have any suggestions as to what could be causing this single table to lose its data? I don't think it's happened more than once to any given server, and I have no clue where to begin looking for the cause. Any suggestions will be greatly appreciated. Thank you. - Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games.
[GENERAL] Indexing questions: Index == key? And index vs substring - how successful?
As the title of this message suggests, I've got a couple of questions about indexing that I'm not sure about. I've tried to take a look at the docs, but I can't remember seeing anything on these; it's quite possible, I admit, that I'm simply not remembering all of what I saw, but I would appreciate it if someone would help me to understand these. 1. Does an indexed column on a table have to be a potential primary key? I've been working with a couple of rather large tables where a common select is on a foreign key called 'cntrct_id' (Varchar(9) in format). However, the same 'cntrct_id' can appear on multiple records in the tables I'm trying to work with now; the tables themselves record events associated with the given 'cntrct_id' record and can store many events for one 'cntrct_id' value. I'd thought that creating an index on the table.cntrct_id field for the event tables would allow me to speed up the transations some, but comparisons of time before and after the indexing lead me to wonder if I was mistaken in this. The times were almost identical in the following areas: Before Indexing, after Indexing but before Analyzing, and after Analyzing. 2. Another common sort on these fields uses part, not all, of the 'cntrct_id' value to search for things; the first character marks original location in an internal framework we're using, for example, and the third character marks the month of the year that the original 'cntrct_id' record was set up. Sorts on either of those are fairly common as well; would indexing on the cntrct_id as a whole be able to speed up a sort on a portion of it? I have in mind something like this: select * from [event table] where substring(cntrct_id, 3,1) = 'H'; which should select any event records associated with 'cntrct_id' values initally set up in August. (Jan = A, Feb = B, etc) If I established an index on the 'cntrct_id' field in the event tables, would it assist in speeding up the substring-based search, or would it not be effective at doing so? Thank you for your assistance. - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
[GENERAL] Select taking excessively long; Request help streamlining.
If this message has already appeared on the list, I apologize. My system tried to temporarily freeze up when I attempted to send this message a few minutes ago, and I do not know if I hit send before it halted or not. I am working with a php program that is designed to enter the database, execute a rather convoluted select (statement seeks out all records in a specific table that match the input criteria, then flows through the table links [x.foo = y.bar] to gather all data related to the records it is looking for), then display the results as a web page. I admit that the primary table the select statement looks at has a large number of records (~ 6 million) in it, but I still don't think it should take as long to accomplish this task as it does. I suspect that the real problem lies in the way I built the select statement, that it is somehow clunky and unwieldy. A copy of the statement and explain results on it appear below. Would someone please assist me in figuring out how to more appropriately streamline this statement? attest=# EXPLAIN select substring(ttrans.tran_dt, 1, 10) as tran_dt, ttrans.dist_id as dist_id, ttrans.cntrct_id as cntrct_id, cntrt.cntrtyp_cd as cntrt_type, cntrt.actual_amt as cntrt_amt, acntrec.mth_reck as mth_reck, persn.frst_nm as fnm, persn.lst_nm as lnm from ttrans, cntrt, acntrec, persn, custm, addru where ttrans.tran_dt = '2007-03-01' and ttrans.tran_dt '2007-03-31' and ttrans.cntrct_id = cntrt.cntrct_id and cntrt.cntrct_seq = addru.cntrct_seq and addru.aunit_seq = acntrec.aunit_seq and (cntrt.cntrtyp_cd = 255 or cntrt.cntrtyp_cd = 260) and cntrt.clnt_seq = custm.clnt_seq and custm.person_seq = persn.person_seq and acntrec.cd_inst = 49 and acntrec.months = 49 and cntrt.dow_flg1 = 'NO' order by ttrans.dist_id asc, cntrt.cntrtyp_cd asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc; QUERY PLAN -- Sort (cost=183688.49..183688.50 rows=1 width=125) Sort Key: ttrans.dist_id, cntrt.cntrtyp_cd, cntrt.cntrct_id - Nested Loop (cost=0.00..183688.48 rows=1 width=125) - Nested Loop (cost=0.00..183683.87 rows=1 width=106) Join Filter: ((inner.cntrct_id)::bpchar = outer.cntrct_id) - Nested Loop (cost=0.00..21820.21 rows=1 width=48) - Nested Loop (cost=0.00..21815.45 rows=1 width=48) - Nested Loop (cost=0.00..21793.06 rows=4 width=43) - Seq Scan on cntrt (cost=0.00..21771.81 rows=4 width=43) Filter: cntrtyp_cd)::text = '255'::text) OR ((cntrtyp_cd)::text = '260'::text)) AND (dow_flg1 = 'NO'::bpchar)) - Index Scan using fk_cntrct on addru (cost=0.00..5.30 rows=1 width=8) Index Cond: (outer.cntrct_seq = addru.cntrct_seq) - Index Scan using fk_aunit on acntrec (cost=0.00..5.59 rows=1 width=13) Index Cond: (outer.aunit_seq = acntrec.aunit_seq) Filter: ((cd_inst = 49) AND ((months)::text = '49'::text)) - Index Scan using pkeyCUSTM on custm (cost=0.00..4.75 rows=1 width=8) Index Cond: (outer.clnt_seq = custm.clnt_seq) - Seq Scan on ttrans (cost=0.00..161492.77 rows=29671 width=58) Filter: ((tran_dt = '2007-03-01 00:00:00-06'::timestamp with time zone) AND (tran_dt '2007-03-31 00:00:00-05'::timestamp with time zone)) - Index Scan using pkeyPERSN on persn (cost=0.00..4.59 rows=1 width=27) Index Cond: (outer.person_seq = persn.person_seq) (21 rows) Thank you for your consideration. - Need Mail bonding? Go to the Yahoo! Mail QA for great tips from Yahoo! Answers users.
[GENERAL] Dumping part (not all) of the data in a database...methods?
I'm needing to do a partial dump on a database. All of the entries in the db can be marked as one of two groups, and I've been asked to create a dump of just the second group. It is possible to do a select statement based dump and just grab the one set of records in the output? - Food fight? Enjoy some healthy debate in the Yahoo! Answers Food Drink QA.
Re: [GENERAL] Dumping part (not all) of the data in a database...methods?
I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was looking for a way to do a 'dump where (select foo where bar = 'criteria')' structure. Merlin Moncure [EMAIL PROTECTED] wrote: On 4/11/07, Andrew Edson wrote: I'm needing to do a partial dump on a database. All of the entries in the db can be marked as one of two groups, and I've been asked to create a dump of just the second group. It is possible to do a select statement based dump and just grab the one set of records in the output? you are aware you can dump a table at a time, right? pg_dump -t foo dumps table foo. A partial dumping scheme would probably involve using pg_dump with various flag in combination with a script that makes a list of things to dump. merlin - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
[GENERAL] Curious situation - running program cannot seem to delete records.
I've been given a file to maintain, the purpose of which is to purge the database of records more than two years old. (Database setup is pg 8.1.3) The program (written in perl) enters postgres as the user 'postgres', and is supposed to select foreign-key records from all tables that link together with a table which has a delete_dt field in it, so long as the delete_dt value (timestamp with time zone) is more than two years old. It then, within a running loop, is supposed to table-by-table delete all records where the value of the key in question matches the returned value. Delete command is simply 'DELETE FROM [table] WHERE [key] = [result variable]'. Result variables are set as [Variable] = $result[x] where $result is the return from the select and x is the relative location of the value in question within the select. The program can apparently enter the database quite nicely, because it's capable of running the initial select statement and receiving results; a set of print statements to the log file that the program creates reveals that it's getting the information. However, it seems to be unable to delete records; going into the database after the program finishes running and running the select statement from within the program yields exactly the same records as doing so before the delete program runs. Does anyone know of anything in Postgres that might be causing this unusual behavior? Or should I check the perl mailing lists instead? Thank you for your consideration. - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
[GENERAL] Select retrieval slowdown after db drop/reload. Suggestions?
I have a select statement, used in a Perl program, which is supposed to find all records related to those in one table which have a delete_dt field value of four years or older. This is the select statement: SELECT t2.dist_id, t1.clnt_seq, t2.cntrct_seq, t2.cntrct_id, t3.aunit_seq, t1.person_seq, t1.addr_seq, t3.addr_seq, t4.frst_nm || ' ' || t4.lst_nm AS name, t5.addr_1, t6.acntdel_dt FROM t1, t2, t3, t4, t5, t6 WHERE t1.clnt_seq = t2.clnt_seq AND t2.cntrct_seq = t3.cntrct_seq AND t3.aunit_seq = t6.aunit_seq AND t1.person_seq = t4.person_seq AND t3.addr_seq = t5.addr_seq AND t1.active_flg =0 AND t2.active_flg =0 AND t3.active_flg = 0 AND t6.acntdel_dt now() - '4 years'::interval order by t2.cntrct_id asc; I'm working on a test box at the moment; the db I am using was made by dumping the production db and copying it over to the test box to be loaded into a newly-created db there. It took a while for me to get the original Perl program working, as I don't really understand Perl, but after I did so, I dropped the db and reloaded it again off of the original files, so I could try another test run and pay attention to what's happening. On the original load of the test db, the query above had a run time of roughly 3, 3.5 minutes before giving results. Considering the size of the db it's searching through, I feel that's fairly reasonable, especially since that's about what the production db does on the same query. Now, after the drop/recreate, the test db is taking somewhat longer to give back its results; just shy of 7 minutes, if I've done the math correctly. (Timing results - Time: 417531.436 ms) I'm the only person working on this particular box at this point. This problem did not start until I reloaded the db from the original files. Obviously, I managed to miss something in the drop/reload process, but I have no clue what. I'm running a vacuum full analyze at the moment; if anyone has any other suggestions as to what I could do to solve this (admittedly minor) problem, I would be grateful to hear them. Thank you for your consideration. - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
[GENERAL] How does filter order relate to query optimization?
I'm working on a php project that's supposed to draw information from the DB for display, and I've been requested to speed up the display as much as possible. I'm drawing data from four tables, with an additional two that I have to 'bounce' through to match the keys together. Also, I've got five direct filtering requirements, four of the 'value = X' type and a date range. My question is this: Would shuffling the placement of the filtering requirements (t1.some_key = t2.some_key and t1.some_other_value = X, etc.) make a difference in processing speed for the response time? And if so, would the response be quicker with the direct (value = x) filters at the front of the WHERE clause, or the end? - Now that's room service! Choose from over 150,000 hotels in 45,000 destinations on Yahoo! Travel to find your fit.
Re: [GENERAL] How does filter order relate to query optimization?
) - Seq Scan on cntrt (cost=0.00..19199.68 rows=2 width=43) (actual time=1590.328..8572.132 rows=68 loops=1) Filter: (((cntrtyp_cd)::text = '260'::text) AND (dow_flg1 = 'NO'::bpchar)) - Index Scan using fk_cntrct on addru (cost=0.00..5.30 rows=1 width=8) (actual time=125.508..125.513 rows=1 loops=68) Index Cond: (outer.cntrct_seq = addru.cntrct_seq) - Index Scan using fk_aunit on acntrec (cost=0.00..5.59 rows=1 width=13) (actual time=117.329..117.340 rows=1 loops=68) Index Cond: (outer.aunit_seq = acntrec.aunit_seq) Filter: ((cd_inst = 49) AND ((months)::text = '49'::text)) - Seq Scan on ttrans (cost=0.00..157710.93 rows=28976 width=58) (actual time=39.742..3530.494 rows=86415 loops=65) Filter: ((tran_dt = '2007-01-01 00:00:00-06'::timestamp with time zone) AND (tran_dt '2007-02-01 00:00:00-06'::timestamp with time zone)) - Index Scan using pkeyCUSTM on custm (cost=0.00..4.75 rows=1 width=8) (actual time=36.492..36.494 rows=1 loops=120) Index Cond: (outer.clnt_seq = custm.clnt_seq) - Index Scan using pkeyPERSN on persn (cost=0.00..4.59 rows=1 width=27) (actual time=26.973..26.981 rows=1 loops=120) Index Cond: (outer.person_seq = persn.person_seq) Total runtime: 271175.640 ms (22 rows) Anybody ideas what might be causing the problems with the slowdown? The slow database is fed by slony logshipping from the 'normal' one, and both are (at least theoretically) getting vacuumed every night. What else might be causing this kind of slowdown problem? Tom Lane [EMAIL PROTECTED] wrote: Andrew Edson writes: I'm working on a php project that's supposed to draw information from the DB for display, and I've been requested to speed up the display as much as possible. I'm drawing data from four tables, with an additional two that I have to 'bounce' through to match the keys together. Also, I've got five direct filtering requirements, four of the 'value = X' type and a date range. My question is this: Would shuffling the placement of the filtering requirements (t1.some_key = t2.some_key and t1.some_other_value = X, etc.) make a difference in processing speed for the response time? No; certainly it will make no difference how you shuffle clauses that involve different sets of tables. If you've got clauses that wind up in the same Filter: condition in the generated plan, and some of them involve expensive functions, it might be useful to shuffle the expensive-to-evaluate ones to the end. But in most cases that's just micro-optimization. Usually what you want to think about for something like this is plan optimization, ie, what order are the tables joined in and with what join methods. Have you looked at EXPLAIN ANALYZE results for the query? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster - Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games.
[GENERAL] Problems shutting down Postmaster
I need to add a system to our postgres pg_hba.conf file; the main server went down a few days ago, and this system was mistakenly left off the list of allowed systems when the server was brought back up. (Older version of the .conf file; I managed to accidentally delete the more current one while trying to copy it for safekeeping. Proves what an idiot I am...) Someone else added the IP address for the other system to the pg_hba.conf file later, but since we had already started postgres by then, it didn't take. And now, for some reason, postgres doesn't seem to want to let me shut down. I keep getting the following error when trying to shut the postmaster down: ./pg_ctl stop -D /usr/local/pgsql/data/ waiting for postmaster to shut down... failed pg_ctl: postmaster does not shut down I am doing this as the postgres user, so I'm not sure what else I might be doing wrong. Does anyone have any kind of suggestions about how to solve this? Alternately, would someone please let me know of other ways to shut down the postmaster so we can restart it with the updated .conf file? Thank you for your consideration. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Re: [GENERAL] Problems shutting down Postmaster
Okay, for some reason we still had a problem connecting after a pg_ctl reload, but the shutdown went through this time, and everything's working now. Thanks for the assistance. Tom Lane [EMAIL PROTECTED] wrote: Andrew Edson writes: Someone else added the IP address for the other system to the pg_hba.conf file later, but since we had already started postgres by then, it didn't take. You don't need to shut down the server to adjust pg_hba.conf ... just pg_ctl reload. And now, for some reason, postgres doesn't seem to want to let me shut down. A standard shutdown doesn't finish until all existing sessions exit of their own accord. Perhaps you wanted -m fast? regards, tom lane - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
