I have performance problem with the following simple update query:
UPDATE posts
SET num_views = num_views + 1
WHERE post_id IN (2526,5254,2572,4671,25);
The table "posts" is a large table with a number of foreign keys (FK).
It seems that the FK triggers for the table are evaluated even th
So the next question is, what pg version is the original poster using?
because 8.1.x doesn't report trigger execution times, and 8.2.x would use
a single bitmap index scan with an = ANY condition, not a BitmapOr.
I have tried 8.1.0 and 8.1.3 for this query.
---(end of br
> Do the rows being updated contain
NULLs in the foreign-key columns?
No, all FK columns are non-NULL. It is very strange.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
I have investigated a bit now and found the following:
When I perform the update the *first* time, the triggers are actually
not evaluated. But from the second update they are.
Also notice that the number of rows changes. Shouldn't that number of
rows always be 2 as question_id is primary key
I have two small queries which are both very fast to evaluate
separately. The first query, "Query 1", calculates some statistics and
the the second query, "Query 2", finds a subset of relevant keys.
When combined into a single query which calculates statistics from only
the subset of relevant ke
There's something odd about that plan. It's doing both a seq scan and a
bitmap scan on "items", but I can't see stats table being mentioned
anywhere.
Huh? Aaah, sorry. I made a major search/replace-refactoring (that
obviously went wrong) on all open files in the editor before posting to
this
You are lying to us about how those queries were posed to Postgres
(and no I don't feel a need to explain how I know).
Sorry. The "lying" was not intended as explained in my reply to Heikku.
Thanks for the tips anyways.
---(end of broadcast)---
T
I have a query that takes about 7000 ms in average to complete the first
time it runs. Subsequent runs complete in only 50 ms. That is more than
a factor 100 faster! How can I make the query perform good in the first
run too?
Query and output from both first and second run of Explain Analyze i
Probably by buying much faster disk hardware.
Or buy more RAM, so that the data can stay cached.
So the only problem here is lack of RAM and/or disk speed?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http:/
As for optimizing the query, I noticed that all three joins are done by
nested loops. I wonder if another join method would be faster. Have you
analyzed all the tables?
Yes. I did a VACUUM FULL ANALYZE before running the test queries. Also I
have just performed an ANALYZE just to be sure ever
I'm wondering why --- doesn't seem like it should take 6400msec to fetch
646 rows, unless perhaps the data is just horribly misordered relative
to the index. Which may in fact be the case ...
Hmm, actually I still don't understand why it takes 6400 ms to fetch the
rows. As far as I can see th
The indexes don't contain visibility information, so Postgres has to look up
the row on disk to verify it isn't dead.
I guess this fact drastically decreases the performance. :-(
The number of rows with a random_number will just grow over time while
the number of questions with status = 1 will
You're essentially asking for a random sample of data that is not
currently in memory. You're not going to get that without some I/O.
No, that sounds reasonable enough. But do you agree with the statement
that my query will just get slower and slower over time as the number of
posts increases
Please post EXPLAIN ANALYZE output for the two queries.
As I wrote in my first post, I pasted this together with the two queries
at pastebin.com:
http://pastebin.com/m3c0d1896
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
Can anyone explain the following odd behavior?
I have a query that completes in about 90 ms. If I append LIMIT to the
very end, eg. "LIMIT 500" the evaluation time increases to about 800 ms.
How can performance get *worse* by giving the database the option to
stop the evaluation earlier (when it
I'm about to buy a combined web- and database server. When (if) the site
gets sufficiently popular, we will split the database out to a separate
server.
Our budget is limited, so how should we prioritize?
* We think about buying some HP Proliant server with at least 4GB ram
and at least a duo
Thanks for all your replies! They are enlightening. I have some
additional questions:
1) Would you prefer
a) 5.4k 2" SATA RAID10 on four disks or
b) 10k 2" SAS RAID1 on two disks?
(Remember the lots (!) of random reads)
2) Should I just make one large partition of my RAID? Does it matter
We are now leaning towards just buying 4 SAS disks.
So should I just make one large RAID-10 partition or make two RAID-1's
having the log on one RAID and everything else on the second RAID?
How can I get the best read/write performance out of these four disks?
(Remember, that it is a combined w
18 matches
Mail list logo
