[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
This apparently didn't go through the first time; so, I'm reposting...
-
Hello!
First off, I'm running 8.0.1 on Win2000 Server. Vacuum analyze is done
every night. Query cost parameters are standard, I've only
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 --an
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
Laurent Martelli <[EMAIL PROTECTED]> writes:
> PFC>SELECT owner from pictures group by owner;
>
> That's a slight improvement, but there's still a seq scan on pictures:
It should be a sequential scan. An index will be slower.
> HashAggregate (cost=114.38..114.38 rows=21 width=4) (ac
Whoa Josh! I don't believe you're going to reduce the cost by 10 times
through a bit of tweaking - not without lowering the sequential scan
cost as well.
The only thing I can think of is perhaps his primary index drastically
needs repacking. Otherwise, isn't there a real anomaly here? Halving t
Josh Berkus 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 seek time to ro
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
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.
Josh Berkus writes:
> Although I can point out that you left out the fact that the disk needs to do
> a seek to find the beginning of the seq scan area, and even then some file
> fragmentation is possible. Finally, I've never seen PostgreSQL manage more
> than 70% of the maximum read rate,
> 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
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 n
> "Merlin" == Merlin Moncure <[EMAIL PROTECTED]> writes:
>> Consider this query:
>>
>> SELECT distinct owner from pictures;
Merlin> [...]
>> Any ideas, apart from more or less manually maintaining a list of
>> distinct owners in another table ?
Merlin> you answered your own qu
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
> "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 comple
> 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 faste
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
Daniel,
> Table "public.descriptionprodftdiclnk"
What is this, German? ;-)
> explain analyze select * from descriptionprodftdiclnk where idword=44;
> QUERY PLAN
> --
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 broadcast)---
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
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
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 perfo
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 pr
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:
"description
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> What about the cache memory on the disk? Even IDE disks have some 8Mb
> cache today, which makes a lot of difference for fairly short scans.
> Even if it's just read cache. That'll bring the speed of random access
> down to a 1=1 relationship with se
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 I
Really? Postgres is generating these queries ???
Dave
Alvaro Herrera wrote:
On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:
David,
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 bef
On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:
David,
> 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 t
Hi,
I'm using ibatis. But in this particular case the sql statement come
from a plain ascii file and it's run by the Ibatis ScriptRunner class.
Beside the fact this class come from ibatis framework it's just plain
sql connection (I'm I wrong???). Just to be sure, here is the code from
the cla
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 sel
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 back
> > >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
> >
[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, chat
33 matches
Mail list logo
