[EMAIL PROTECTED] mentioned :
= Try ANALYZE after loading the referenced tables, but before loading the main
table
I attached a new script for creating the load file...
Analyze didn't help, it actually took longer to load.
I set autocommit to off, and put a commit after every 100
inserts,
The this day and age argument isn't very convincing. Hard drive
capacity growth has far outstripped hard drive seek time
and bandwidth improvements.
Random access has more penalty than ever.
In point of fact, there haven't been noticeable seek time
improvements
for years.
Hi All,
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued. For what I
understand the performance problem seem to came from those selects that
point
David Gagnon wrote:
Hi All,
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued. For what I
understand the performance problem seem to came from those
Hi
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued. For what I
understand the performance problem seem to came from those selects that
point back to
Hello.
I have a problem concerning multi-column indexes.
I have a table containing some 250k lines.
Table public.descriptionprodftdiclnk
Column| Type | Modifiers
-+-+---
idword | integer | not null
idqualifier | integer | not null
Indexes:
On Wed, 16 Mar 2005, David Gagnon wrote:
Hi
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued. For what I
understand the performance problem seem to
Stephan Szabo wrote:
On Wed, 16 Mar 2005, David Gagnon wrote:
Hi
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM IC statement is issued. For what I
understand the
Consider this query:
SELECT distinct owner from pictures;
Unique (cost=361.18..382.53 rows=21 width=4) (actual time=14.197..17.639
rows=21 loops=1)
- Sort (cost=361.18..371.86 rows=4270 width=4) (actual
time=14.188..15.450 rows=4270 loops=1)
Sort Key: owner
- Seq
Greg,
So 800kB/s for random access reads. And 40Mb/s for sequential reads. That's
a factor of 49. I don't think anyone wants random_page_cost to be set to 50
though.
For a high end 15k drive I see average seek times get as low as 3ms. And
sustained transfer rates get as high as 100Mb/s. So
Try :
SELECT owner from pictures group by owner;
Any ideas, apart from more or less manually maintaining a list of
distinct owners in another table ?
That would be a good idea too for normalizing your database.
---(end of
Daniel,
Table public.descriptionprodftdiclnk
What is this, German? ;-)
explain analyze select * from descriptionprodftdiclnk where idword=44;
QUERY PLAN
---
On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote:
Consider this query:
SELECT distinct owner from pictures;
The performance has nothing to do with the number of rows returned, but
rather the complexity of calculations and amount of data to sift through
in order to find it.
Any
Consider this query:
SELECT distinct owner from pictures;
[...]
Any ideas, apart from more or less manually maintaining a list of
distinct owners in another table ?
you answered your own question. With a 20 row owners table, you should
be directing your efforts there group by is faster
Rod == Rod Taylor [EMAIL PROTECTED] writes:
Rod On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote:
Consider this query:
SELECT distinct owner from pictures;
Rod The performance has nothing to do with the number of rows
Rod returned, but rather the complexity of
Wow, what a fast response !!!
PFC == PFC [EMAIL PROTECTED] writes:
PFC Try :
PFC SELECT owner from pictures group by owner;
That's a slight improvement, but there's still a seq scan on pictures:
HashAggregate (cost=114.38..114.38 rows=21 width=4) (actual time=7.585..7.605
rows=21
On Wed, 2005-03-16 at 19:31 +0100, Laurent Martelli wrote:
Rod == Rod Taylor [EMAIL PROTECTED] writes:
Rod On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote:
Consider this query:
SELECT distinct owner from pictures;
Rod The performance has nothing to do with the
I just wished there was a means to fully automate all this and render
it transparent to the user, just like an index.
Merlin Voila! Merlin p.s. normalize your data always!
I have this:
pictures(
PictureID serial PRIMARY KEY,
Owner integer NOT NULL REFERENCES users,
Hi all,
Could someone explain me when I joined tree tables the querys that took
about 1sec to finish, takes 17secs to complete when I put tree tables joined
?
If I join movest/natope, it's fast, if I join movest/produt, it's fast too,
but when I put a third joined table, forget, it's very slow.
On Wed, 16 Mar 2005, David Gagnon wrote:
Stephan Szabo wrote:
On Wed, 16 Mar 2005, David Gagnon wrote:
Hi
I rerun the example with the debug info turned on in postgresl. As you
can see all dependent tables (that as foreign key on table IC) are
emptied before the DELETE FROM
Josh Berkus josh@agliodbs.com writes:
So what's going on with the empirically derived value of 4?
It's not empirically derived;
Yes it is. I ran experiments back in the late 90s to derive it.
Check the archives.
Disks have gotten noticeably bigger since then, but I don't think
the ratio of
David Brown [EMAIL PROTECTED] writes:
Actually, I'm surprised the planner came up with such a low cost for the
single column index, unless ... perhaps correlation statistics aren't
used when determining costs for multi-column indexes?
The correlation calculation for multi-column indexes is
On Wed, Mar 16, 2005 at 05:10:17PM -0300, Rodrigo Moreno wrote:
If I join movest/natope, it's fast, if I join movest/produt, it's fast too,
but when I put a third joined table, forget, it's very slow.
What version of PostgreSQL are you using?
All tables are vacuumed by vacummdb --full
[EMAIL PROTECTED] writes:
So, it would seem like my optimal plan should have hash joins with index
scans.
No. The thing you are looking at here is a nestloop join with inner
index scan, which has to be understood as a unit even though EXPLAIN
doesn't describe it that way. The inner indexscan
24 matches
Mail list logo
