Re: [PERFORM] Horribly slow query/ sequential scan
I don't think I understand the idea behind this query. Do you really need billing_reports twice? > The query: > explain analyze select > w.appid,w.rate,w.is_subscribed,sum(w.hits) AS Hits ,sum(w.sius) AS IUs, > sum(w.total_amnt) AS Total,sum(w.hits) * w.rate AS ByHits, > sum(w.sius) * w.rate AS BYIUS > from bill_rpt_work w, billing_reports b > where w.report_id in > (select b.report_id from billing_reports where b.report_s_date = > '2006-09-30') > and (w.client_id = '22741' or w.client_id = '227410') > group by 1,2,3 > order by 1,2,3; Maybe this is the query you want instead? select w.appid, w.rate, w.is_subscribed, sum(w.hits) AS Hits, sum(w.sius) AS IUs, sum(w.total_amnt) AS Total, sum(w.hits) * w.rate AS ByHits, sum(w.sius) * w.rate AS BYIUS from bill_rpt_work w where w.report_id in (select b.report_id from billing_reports b where b.report_s_date = '2006-09-30') and (w.client_id = '22741' or w.client_id = '227410') group by 1,2,3 order by 1,2,3; /Dennis ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [Again] Postgres performance problem
> Last time I had this problem i solved it stopping website, restarting > database, vacuumm it, run again website. But I guess this is going to > happen again. > > I would like to detect and solve the problem. Any ideas to detect it? Do you have very long transactions? Maybe some client that is connected all the time that is idle in transaction? /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Long Running Commits - Not Checkpoints
> I'm having a problem with long running commits appearing in my database > logs. It may be hardware related, as the problem appeared when we moved > the database to a new server connected to a different disk array. The > disk array is a lower class array, but still more than powerful enough > to handle the IO requirements. One big difference though is that the > old array had 16 GB of cache, the new one has 4 GB. Maybe the old disk array had battery backed up ram that was used as a write cache and the new only has read cache? Without battery backed up ram (or flash or whatever) then a commit need to flush data down onto the pysical disk. /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!
> Old servers that housed 7.4 performed better than 8.1.4 version...are > there any MAJOR performance hits with this version??? > > I set the postgresql.conf setting to equal that of 7.4 and queries still > run > SLOW on 8.1.4... We need to find a specific query that is slow now that was fast before, and see the EXPLAIN ANALYZE of that query. If you have the old server still around then showing the EXPLAIN ANALYZE of the same query on that server would be a lot of help. /Dennis ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Optimizing No matching record Queries
On 2008-02-12 13:35, Pallav Kalva wrote:
Hi,
...
Table Definitions
\d listing.listingstatus
Table "listing.listingstatus"
Column |Type
| Modifiers
-+-+
listingstatusid | integer | not null default
nextval(('listing.listingstatusseq'::text)::regclass)
shortname | text|
longname| text|
_entrydate | timestamp without time zone | default
('now'::text)::timestamp(6) without time zone
Indexes:
"pk_listingstatus_listingstatusid" PRIMARY KEY, btree
(listingstatusid), tablespace "indexdata"
Since you are searching by "shortname", trying adding an index on that.
Although with that tiny a table, it might not matter.
The questions are:
1. Why in the planner scanning the entire idx_listing_entrydate, when
I'd think it should be scanning the entire
pk_listingstatus_listingstatusid ?
2. Why is "Index Scan using pk_listingstatus_listingstatusid on
listingstatus listingsta1_ (cost=0.00..0.27 rows=1 width=4) (never
executed)" ?
Note: I'm new at this as well, and jumped in to learn as well as to help.
-- Dean
--
Mail to my list address MUST be sent via the mailing list. All other mail will
bounce.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Q on views and performance
On 2008-02-22 12:49, Kynn Jones wrote: Of course, I expect that using views V and V... would result in a loss in performance relative to a version that used bona fide tables T and T. My question is, how can I minimize this performance loss? That used to be my thoughts too, but I have found over the years that the PostgreSQL execution planner is able to "flatten" SELECTs using VIEWs, ALMOST ALWAYS in a way that does not adversely affect performance, and often gives an IMPROVEMENT in performance, probably because by using VIEWs I am stating the query problem in a better way than if I try to guess the best way to optimize a SELECT. I have at least a 10:1 ratio of VIEWs to TABLEs. Occasionally, with some query that is slow, I will try to rewrite it without VIEWs. This ALMOST NEVER results in an improvement in performance, and when it does, I am able to find another way to write the VIEW and SELECT to recapture the gain. -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. ---(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] Q on views and performance
On 2008-02-23 05:59, Kynn Jones wrote: On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: ... Since you have experience working with views, let me ask you this. The converse strategy to the one I described originally would be to create the individual tables T1, T2, T3, ..., T100, but instead of keeping around the original (and now redundant) table T, replace it with a view V made up of the union of T1, T2, T3, ..., T100. The problem with this alternative is that one cannot index V, or define a primary key constraint for it, because it's a view. This means that a search in V, even for a primary key value, would be *have to be* very inefficient (i.e. I don't see how even the very clever PostgreSQL implementers could get around this one!), because the engine would have to search *all* the underlying tables, T1 through T100, even if it found the desired record in T1, since it has no way of knowing that the value is unique all across V. Is there a way around this? kynn Oh, I wouldn't create separate tables and do a UNION of them, I'd think that would be inefficient. I didn't look in detail at your previous eMail, but I will now: 1. You haven't told us the distribution of "zipk", or what the tables are indexed on, or what type of performance you are expecting. Your initial examples don't help much unless you actually have performance numbers or EXPLAIN output for them, since adding the third JOIN significantly changes the picture, as does changing one of the JOINs to a LEFT JOIN. 2. In your actual (Q1** and Q2**) examples, why is one JOIN an INNER JOIN and the other one a LEFT JOIN? Given your description of Q1 at the top of your message, that doesn't make sense to me. 3. Why not write: CREATE VIEW txt AS SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2 FROM T a1 [LEFT] JOIN T a2 USING( zipk ); -- Use "LEFT" if appropriate SELECT word1, word1 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; If either of those (either with or without the "LEFT") are not equivalent to your problem, how about just: SELECT a1.word AS word1, a2.word AS word2 FROM S JOIN T a1 USING( word) [LEFT] JOIN T a2 USING( zipk ) -- Use "LEFT" if appropriate WHERE a1.type = AND a2.type = ; Show us (using EXPLAIN) what the query planner thinks of each of these. -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [PERFORM] Q on views and performance
On 2008-02-23 07:08, Dean Gibson (DB Administrator) wrote: ... SELECT word1, word1 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; ... Oops that should be: SELECT word1, word2 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [PERFORM] Q on views and performance
On 2008-02-23 08:21, Kynn Jones wrote: ... 3. Why not write: CREATE VIEW txt AS SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2 FROM T a1 [LEFT] JOIN T a2 USING( zipk ); -- Use "LEFT" if appropriate SELECT word1, word1 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; This is would indeed produce the same results as Q1, but this approach would require defining about 10,000 views, one for each possible pair of int1 and int2 Why 10,000 views??? What's wrong with the ONE view above? You DON'T want to be defining VIEWs based on actual tables VALUES; leave that to the SELECT. For that matter, what's wrong with the final SELECT I listed (below)? SELECT a1.word AS word1, a2.word AS word2 FROM S JOIN T a1 USING( word ) LEFT JOIN T a2 USING( zipk ) WHERE a1.type = AND a2.type = ; -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [PERFORM] Q on views and performance
On 2008-02-23 08:49, Dean Gibson (DB Administrator) wrote: Why 10,000 views??? What's wrong with the ONE view above? You DON'T want to be defining VIEWs based on actual tables VALUES; leave that to the SELECT. For that matter, what's wrong with the final SELECT I listed (below)? SELECT a1.word AS word1, a2.word AS word2 FROM S JOIN T a1 USING( word ) LEFT JOIN T a2 USING( zipk ) WHERE a1.type = AND a2.type = ; -- Dean Amendment: I forgot, that if it's a LEFT JOIN you have to write it as: SELECT a1.word AS word1, a2.word AS word2 FROM S JOIN T a1 USING( word ) LEFT JOIN T a2 USING( zipk ) WHERE a1.type = AND (a2.type = OR a2.type IS NULL); -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [PERFORM] Bypassing useless ORDER BY in a VIEW
On 2008-02-28 09:13, Tom Lane wrote: A rule of thumb is that ORDER BY in a view is bad design, IMHO. regards, tom lane I was surprised to find out that apparently it's also a PostgreSQL extension; standard SQL apparently disallows ORDER BY in VIEWs: http://en.wikipedia.org/wiki/Order_by_(SQL) When I found this out, I removed all the ORDER BYs from my VIEWs (which had been there for the convenience of subsequent SELECTs). Of course, where ORDER BY in a VIEW is really helpful, is with OFFSET and/or LIMIT clauses (which are also PostgreSQL extensions), which is equivalent to what you point out. -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. ---(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
