Обсуждение: I don't understand this explain output

Hi,

I am looking at a very simple query (select * from queue),
which takes forever to finish. I don't understand the output that
explain tells me:

m=# explain select * from queue;
                          QUERY PLAN
------------------------------------------------------------
  Seq Scan on queue  (cost=0.00..635477.00 rows=1 width=402)
(1 row)

The cost seems to be very high. It's a small table (only 23 rows)
and has not seen many modifications since the table was initially
filled. OTOH many individual records are updated every minute.


If I look at anopther table in the same database:

m=# explain select * from contact;
                          QUERY PLAN
-------------------------------------------------------------
  Seq Scan on contact  (cost=0.00..183.14 rows=614 width=411)
(1 row)

and this query finishes very quickly (614 rows).


Why would a simple query be so very costly?

Thanks,
Ron Arts

--
NeoNova BV
innovatieve internetoplossingen

http://www.neonova.nl  Science Park 140           1098 XG Amsterdam
info: 020-5611300      servicedesk: 020-5611302   fax: 020-5611301
KvK Amsterdam 34151241

Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer

Ron Arts wrote:
> m=# explain select * from queue;
>                          QUERY PLAN
> ------------------------------------------------------------
>  Seq Scan on queue  (cost=0.00..635477.00 rows=1 width=402)
> (1 row)
>
> The cost seems to be very high. It's a small table (only 23 rows)
> and has not seen many modifications since the table was initially
> filled. OTOH many individual records are updated every minute.



Then the inidividual records have been updated 635,477 times; because
that's how many rows are really in the table.  Each time a record is
updated, the old one is still in database and a new one is added.  Run
"VACUUM FULL ANALYSE" on a regular basis to remove the obsolete records
from table and recover disk space.

Since 8.1, there is an optional feature called autovacuum.

thanks for the answer, I forgot to mention that I did
enable autovacuum in postgresql.conf a while ago.
(autovacuum = on). But that did not seem to help.

I will run autovacuum full tonight. So the cost means
the total number of records to read including deleted ones?

Ron


Frank Bax schreef:
> Ron Arts wrote:
>> m=# explain select * from queue;
>>                          QUERY PLAN
>> ------------------------------------------------------------
>>  Seq Scan on queue  (cost=0.00..635477.00 rows=1 width=402)
>> (1 row)
>>
>> The cost seems to be very high. It's a small table (only 23 rows)
>> and has not seen many modifications since the table was initially
>> filled. OTOH many individual records are updated every minute.
>
>
>
> Then the inidividual records have been updated 635,477 times; because
> that's how many rows are really in the table.  Each time a record is
> updated, the old one is still in database and a new one is added.  Run
> "VACUUM FULL ANALYSE" on a regular basis to remove the obsolete records
> from table and recover disk space.
>
> Since 8.1, there is an optional feature called autovacuum.
>


--
NeoNova BV
innovatieve internetoplossingen

http://www.neonova.nl  Science Park 140           1098 XG Amsterdam
info: 020-5611300      servicedesk: 020-5611302   fax: 020-5611301
KvK Amsterdam 34151241

Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer

Ron Arts <ron.arts@neonova.nl> writes:
> thanks for the answer, I forgot to mention that I did
> enable autovacuum in postgresql.conf a while ago.
> (autovacuum = on). But that did not seem to help.

Once the table is bloated, autovacuum won't do much to de-bloat it.
You need a one-time cleanup to get rid of the bloat, and then
hopefully autovac will keep things under control after that.

VACUUM FULL and then REINDEX would do for cleanup.  On large
tables it would be better to use CLUSTER, but with only 23 live
rows, VACUUM FULL will be fast enough.

> I will run autovacuum full tonight. So the cost means
> the total number of records to read including deleted ones?

No, it's more like the total number of disk pages to read.  The
previous claim that it was equal to the number of dead rows is
erroneous.

            regards, tom lane

Tom Lane schreef:
> Ron Arts <ron.arts@neonova.nl> writes:
>> thanks for the answer, I forgot to mention that I did
>> enable autovacuum in postgresql.conf a while ago.
>> (autovacuum = on). But that did not seem to help.
>
> Once the table is bloated, autovacuum won't do much to de-bloat it.
> You need a one-time cleanup to get rid of the bloat, and then
> hopefully autovac will keep things under control after that.
>
> VACUUM FULL and then REINDEX would do for cleanup.  On large
> tables it would be better to use CLUSTER, but with only 23 live
> rows, VACUUM FULL will be fast enough.
>
>> I will run autovacuum full tonight. So the cost means
>> the total number of records to read including deleted ones?
>
> No, it's more like the total number of disk pages to read.  The
> previous claim that it was equal to the number of dead rows is
> erroneous.
>
>             regards, tom lane

Tom and others. Yes, vacuum full did the trick:

INFO:  "queue": moved 17 row versions, truncated 635478 to 2 pages

m=# explain select * from queue;
                       QUERY PLAN
--------------------------------------------------------
Seq Scan on queue  (cost=0.00..2.21 rows=21 width=403)
(1 row)


BIG difference.

Thanks a lot everyone. Especially you Tom, I have great respect
for the amount of work you put in answering all these questions.

Ron

--
NeoNova BV
innovatieve internetoplossingen

http://www.neonova.nl  Science Park 140           1098 XG Amsterdam
info: 020-5611300      servicedesk: 020-5611302   fax: 020-5611301
KvK Amsterdam 34151241

Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer