Tom Lane a écrit :
Arnaud Lesauvage [EMAIL PROTECTED] writes:
Indeed, the new query does not perform that well :
Hash Join (cost=112.75..307504.97 rows=2024869 width=355) (actual
time=53.995..246443.811 rows=2020061 loops=1)
...
Total runtime: 2777844.892 ms
I removed all unnecessary
Tom Lane a écrit :
Arnaud Lesauvage [EMAIL PROTECTED] writes:
I must be missing something, so here is the full table description.
It looks pretty harmless, except for
CREATE TRIGGER parse_log_trigger
BEFORE INSERT
ON statistiques.log
FOR EACH ROW
EXECUTE PROCEDURE
Tom Lane a écrit :
I think the conclusion must be that there was just too much I/O to be
done to update all the rows. Have you done any tuning of shared_buffers
and so forth? I recall having seen cases where update performance went
bad as soon as the upper levels of a large index no longer fit
Hi list !
I am running a query to update the boolean field of a table based on
another table's fields.
The query is (changed names for readability):
UPDATE t1
SET booleanfield = (t2.field1 IN ('some', 'other') AND t2.field2 = 'Y')
FROM t2
WHERE t1.uid = t2.uid
t2.uid is the PRIMARY KEY.
t2
Ragnar a écrit :
Do you think the problem is with the indexes ?
I guess so. are you sure about the index on t1.uid?
what are the column definitions for t1.uid and t2.uid ?
are they the same ?
Man, no !!!
I just checked and indeed, no index on this column. I probably dropped
it lately.
Jens Schipkowski a écrit :
the problem is a combination of bad formed SQL and maybe missing indexes.
try this:
UPDATE t1
SET booleanfield = foo.bar
FROM (SELECT uid,(field IN ('some','other') AND field2 = 'Y') AS bar FROM
t2) AS foo
WHERE t1.uid=foo.uid;
Hi Jens,
Why is this query better
Jens Schipkowski a écrit :
On Wed, 13 Dec 2006 13:23:41 +0100, Arnaud Lesauvage [EMAIL PROTECTED]
Why is this query better than the other one ? Because it runs the
(field IN ('some','other') AND field2 = 'Y') once and then executes
the join with the resulting set ?
True. The Subselect
Ragnar a écrit :
On mið, 2006-12-13 at 14:38 +0100, Arnaud Lesauvage wrote:
Jens Schipkowski a écrit :
On Wed, 13 Dec 2006 13:23:41 +0100, Arnaud Lesauvage [EMAIL PROTECTED]
Why is this query better than the other one ? Because it runs the
(field IN ('some','other') AND field2 = 'Y
asif ali a écrit :
Arnaud,
Have you run ANALYZE on the table after creating index?
Yes, I have !
Also make sure that #effective_cache_size is set properly. A higher
value makes it more likely to use index scans.
It is set to 50.000. I thought this would be enough, and maybe too
Markus Schiltknecht a écrit :
What's common practice? What's it on the pgsql mailing lists?
The netiquette usually advise mailers to wrap after 72 characters
on mailing lists.
This does not apply for format=flowed I guess (that's the format
used in Steinar's message).
Hi list !
I have two table with a 2-column index on both of them.
In the first table, the first colum of the index is the primary key,
the second one is an integer field.
In the second table, the two columns are the primary key.
When I join these two tables, the 2-column index of the first
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
I have two table with a 2-column index on both of them.
In the first table, the first colum of the index is the primary key, the
second one is an integer field.
In the second table, the two columns are the primary key.
When I join these two
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
This query was taken from my adminsitrative areas model (continents,
countries, etc...). Whenever I query this model, I have to join many
tables.
I don't really know what the overhead of reading the heap-tuples is, but
would it be a good
Heikki Linnakangas a écrit :
Arnaud Lesauvage wrote:
It is quite typical, yes. It is the base query of a view. In fact, most
views have a lot more joins (they join with all the upper-level tables).
But 150ms is OK, indeed.
If the query using the view does anything more than a SELECT * FROM
Tom Lane a écrit :
Arnaud Lesauvage [EMAIL PROTECTED] writes:
When I join these two tables, the 2-column index of the first table is
not used.
Why does the query planner think that this plan is better ?
Hm, is gid by itself nearly unique in these tables? If so, the merge
join would get
Hi List !
I have a performance problem, but I am not sure whether it really
is a problem or not.
I am running a fresh install of PostgreSQL 8.1.4 on Windows2000.
The server is a bi-opteron with 2GB of RAM. The PostgreSQL's data
folder is on a RAID-0 array of 2 SATA WD Raptor drives (10.000
rpm,
Steinar H. Gunderson wrote:
Total runtime: 0.801 ms
0.801 ms is _far_ under a second... Where do you have the latter timing from?
I fell stupid...
Sorry for the useless message...
[]
---(end of broadcast)---
TIP 2: Don't 'kill -9'
Tobias Brox wrote:
Oh, the gid is not primary key. I guess I should also apologize for
adding noise here :-)
Yes, it is a primary key, but I am the noise maker here ! ;-)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
Tobias Brox wrote:
[Arnaud Lesauvage - Tue at 02:13:59PM +0200]
Tobias Brox wrote:
Oh, the gid is not primary key. I guess I should also apologize for
adding noise here :-)
Yes, it is a primary key, but I am the noise maker here ! ;-)
Oh - it is. How can you have a default value
19 matches
Mail list logo
