I will report back on this and attempt to give the particulars. It
will take 24 hours due to other time commitments.
Thank you very much for explaining :) this to me.
When I used only the first 10,000 rows of the 100+ thousand rows in
the original table (of two tables) I was working with, I got the
result I wanted in 10 minutes, which was really probably 80% of what I
wanted. Nevertheless, I do not want to fly blind in the future.
John
On Mar 29, 2010, at 7:10 PM, Bill Moran wrote:
In response to Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx>:
Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
No, not really. But you can (and should) run EXPLAIN <your query>
to
obtain the execution plan for that query, und you can show us
this plan
(and the table-definition for all included tables). Maybe someone
is able
to tell you what you can do to speed up your query.
To piggyback on this ... EXPLAIN _is_ the way to know how long your
query will take, but keep in mind it's only an _estimate_.
Given that, in my experience EXPLAIN is pretty accurate 90% of the
time, as long as you analyze frequently enough.
As far as i know, EXPLAIN _can't_ say how long a query will take, it
returns only a COST, not a TIME.
Correct.
Or can you tell me how long this query will be take?
test=# explain select * from foo;
QUERY PLAN
-------------------------------------------------------
Seq Scan on foo (cost=0.00..34.00 rows=2400 width=4)
(1 Zeile)
EXPLAIN ANALYZE a few other queries of various complexity, and I'll be
able to translate that estimate to a time.
No, it's not 100% accurate, but (as I stated earlier) in my
experience,
it gives you a pretty good idea.
Okay, it's a really little table and a really simple plan ... but
imagine, i have a table with 100 millions rows and a) a slow disk
and b)
a fast SSD.
You're absolutely correct, and that's something that I should not have
omitted from my previous response. Translating the cost into a time
estimate is highly hardware-dependent, and not 100% accurate, so run
some tests to get an idea of what your cost -> time ratio is, and take
those cost estimates with a grain of salt.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
