<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
Neil Conway wrote:<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap="">On Mon, 15 Apr 2002 13:07:20 -0400<br>"Dmitry Tkach" <a class="moz-txt-link-rfc2396E" href="mailto:dmitry(at)openratings(dot)com"><dmitry(at)openratings(dot)com></a> wrote:<br></pre>
<blockquote type="cite">
<pre wrap="">Hi, everybody!<br></pre>
</blockquote>
<pre wrap=""><!----><br>Hi Dmitry! Don't cross-post! It's annoying!<br></pre>
</blockquote>
What do you mean by 'cross-post'?<br>
Are you saying that posting to several lists at a time is annoying?<br>
I just thought, that this problem might be interesting to people, who read
those (and not necessarily ALL <br>
of them)... What's annoying about it?<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br></pre>
<blockquote type="cite">
<pre wrap="">This took me awfully long, but worked (I guess).<br>I say 'I guess', because I wasn't able so far to verify that - when I triued to do<br><br>select * from a limit 1;<br><br>It just hungs on me ... at least, it looks like it does.<br></pre>
</blockquote>
<pre wrap=""><!----><br>This didn't hang, it just requires a sequential scan of the whole table.</pre>
</blockquote>
I know it does (as I said below). The point is that it SHOULD NOT, and especially,
that I can't imagine anyone, not familiar with postgres internals to expect
that it would - all it needs to do is to grab the first row and return immediately.<br>
That's what it would do, if you just create a new table and populate it with
data.<br>
<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br>As you observe below, it will also need to scan through dead tuples,</pre>
</blockquote>
Not 'also' - JUST the dead ones! That's what's especially annoying about
it - as soon as it finds the first tuple, that's not dead, it returns.<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br>but that is just a product of MVCC and there's no real way around<br>it.</pre>
</blockquote>
My whole point is that I don't believe it (that there is no way around) :-)<br>
For one thing, I have never seen ANY database engine (Oracle, Informix, DB2)
that would take more than a second to get the first row from a table, regardless
of what has been done to that table before.<br>
That (and my common sense too) tells me that there MUST be a 'way around
it'. <br>
I can see, that it's not currently implemented in postgres, but do believe
(and that's the whole point of me posting that message in the first place)
that it is a huge usability issue and really needs to be fixed.<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""> Once you VACUUM the dead tuples will be removed and sequential<br>scans should be fast once more.<br></pre>
</blockquote>
<br>
Yeah... I hope so. I am running vacuum on that table. It's been running for
6 hours now and still has not finished. <br>
Doesn't it look to you like a little too much trouble to go through just
to take a look at the first row of a table ? :-)<br>
<br>
And, once again, I am not done modifying that schema - this is just an intermediate
step, which means, I will have to do the vacuum all over when I am finished...<br>
<br>
This seems like WAY too much trouble to me :-(<br>
<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br>And before assuming that something has hung, it's a good idea to<br>look at the output of EXPLAIN for that query, as well as monitoring<br>system performance (through top, vmstat, etc) to see what the<br>system is doing.<br></pre>
</blockquote>
Yeah, right...<br>
<br>
explain select * from a limit 1;<br>
NOTICE: QUERY PLAN:<br>
<br>
Limit (cost=0.00..1.01 rows=1 width=46)<br>
-> Seq Scan on a (cost=0.00..32529003.00 rows=32243660 width=46)<br>
<br>
EXPLAIN<br>
<br>
There is absolutely nothing in this plan, that would suggest it will go on
executing for ages...<br>
Look at the 'cost' value for example...<br>
In any event, there is nothing different in this plan from what I was getting
before I modified the table (when the query would take just a few milliseconds
to be executed).<br>
<br>
As for monitoring system performance... Well, I could see it maxing out on
CPU usage and disk IO at times... How exactly does it help me to realize
it did not hung?<br>
<br>
(Let me clarify that - by 'hung' I mean 'not going to return the results
in any reasonable time', not necessarily 'not doing anything at all')<br>
<br>
<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br></pre>
<blockquote type="cite">
<pre wrap="">Lucky me, I have compiled the backend from sources with full debug info, because if I hadn't done that,<br>(as most users), I would certainly had thought, that my database is hopelessly corrupted, and would have to<br>recreate it from scratch :-(<br></pre>
</blockquote>
<pre wrap=""><!----><br>That's a ludicrous conclusion.<br></pre>
</blockquote>
<br>
Why is it?<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br></pre>
<blockquote type="cite">
<pre wrap="">First of all, a question for you - is ANY update to a table equivalent (in this respect) to a delete+insert?<br></pre>
</blockquote>
<pre wrap=""><!----><br>Yes, AFAIK -- MVCC requires this.<br></pre>
</blockquote>
<br>
What's MVCC?<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br></pre>
<blockquote type="cite">
<pre wrap="">- Vacuum, isn't the speediest thing in the world too (it's been running for a hour now, and still has not finished).<br></pre>
</blockquote>
<pre wrap=""><!----><br>Is this 7.2? If not, VACUUM should be substantially faster in 7.2.</pre>
</blockquote>
Yes, it is 7.2<br>
<blockquote type="cite" cite="mid:20020415142451(dot)1d8a21d0(dot)nconway(at)klamath(dot)dyndns(dot)org">
<pre wrap=""><br>In any case, you'll always want to VACUUM or VACUUM FULL (and<br>ANALYZE) when you change your tables in such a dramatic fashion.<br><br></pre>
</blockquote>
<br>
I know... Once again, I was hoping to be able to complete my changes before
doing the vacuum :-(<br>
<br>
Thanks for your reply!<br>
<br>
Dima<br>
<br>
<br>
</body>
</html>