Neil Conway wrote:
On Mon, 15 Apr 2002 13:07:20 -0400
"Dmitry Tkach" <dmitry@openratings.com> wrote:
Hi, everybody!
Hi Dmitry! Don't cross-post! It's annoying!
What do you mean by 'cross-post'?
Are you saying that posting to several lists at a time is annoying?
I just thought, that this problem might be interesting to people, who read
those (and not necessarily ALL
of them)... What's annoying about it?
This took me awfully long, but worked (I guess).
I say 'I guess', because I wasn't able so far to verify that - when I triued to do
select * from a limit 1;
It just hungs on me ... at least, it looks like it does.
This didn't hang, it just requires a sequential scan of the whole table.
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.
That's what it would do, if you just create a new table and populate it with
data.
As you observe below, it will also need to scan through dead tuples,
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.
but that is just a product of MVCC and there's no real way around
it.
My whole point is that I don't believe it (that there is no way around) :-)
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.
That (and my common sense too) tells me that there MUST be a 'way around
it'.
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.
Once you VACUUM the dead tuples will be removed and sequential
scans should be fast once more.
Yeah... I hope so. I am running vacuum on that table. It's been running for
6 hours now and still has not finished.
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 ? :-)
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...
This seems like WAY too much trouble to me :-(
And before assuming that something has hung, it's a good idea to
look at the output of EXPLAIN for that query, as well as monitoring
system performance (through top, vmstat, etc) to see what the
system is doing.
Yeah, right...
explain select * from a limit 1;
NOTICE:š QUERY PLAN:
Limitš (cost=0.00..1.01 rows=1 width=46)
š ->š Seq Scan on aš (cost=0.00..32529003.00 rows=32243660 width=46)
EXPLAIN
There is absolutely nothing in this plan, that would suggest it will go on
executing for ages...
Look at the 'cost' value for example...
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).
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?
(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')
Lucky me, I have compiled the backend from sources with full debug info, because if I hadn't done that,
(as most users), I would certainly had thought, that my database is hopelessly corrupted, and would have to
recreate it from scratch :-(
That's a ludicrous conclusion.
Why is it?
First of all, a question for you - is ANY update to a table equivalent (in this respect) to a delete+insert?
Yes, AFAIK -- MVCC requires this.
What's MVCC?
- Vacuum, isn't the speediest thing in the world too (it's been running for a hour now, and still has not finished).
Is this 7.2? If not, VACUUM should be substantially faster in 7.2.
Yes, it is 7.2
In any case, you'll always want to VACUUM or VACUUM FULL (and
ANALYZE) when you change your tables in such a dramatic fashion.
I know... Once again, I was hoping to be able to complete my changes before
doing the vacuum :-(
Thanks for your reply!
Dima