Thread: EXPLAIN output explanation requested
Hello all,
I have an EXPLAIN statement that gives me output I understand,
but on the other hand I don't...
tium=# explain select codec1, phonetype from phone;
QUERY PLAN
------------------------------------------------------------
Seq Scan on phone (cost=0.00..85882.58 rows=658 width=11)
(1 row)
This is a table with 658 rows. Queries are indeed very
slow. How is the query plan computed? What does the 85882 value
mean?
Thanks,
Ron
--
NeoNova BV, The Netherlands
Professional internet and VoIP solutions
http://www.neonova.nl Kruislaan 419 1098 VA Amsterdam
info: 020-5628292 servicedesk: 020-5628292 fax: 020-5628291
KvK Amsterdam 34151241
The following disclaimer applies to this email:
http://www.neonova.nl/maildisclaimer
am Tue, dem 03.06.2008, um 11:56:39 +0200 mailte Ron Arts folgendes: > Hello all, > > I have an EXPLAIN statement that gives me output I understand, > but on the other hand I don't... > > tium=# explain select codec1, phonetype from phone; > QUERY PLAN > ------------------------------------------------------------ > Seq Scan on phone (cost=0.00..85882.58 rows=658 width=11) > (1 row) > > > This is a table with 658 rows. Queries are indeed very > slow. How is the query plan computed? What does the 85882 value > mean? You have no WHERE-condition, the whole table read with a sequential scan. The 85882.58 is a calculated cost, based on seq_page_cost=1. In other word, PG has to read about. 85882 blocks from disk, with a block-size of 4 KByte thats about 343 MByte. I guess, you have much insert/delete or update - operations on this table and no recent vacuum. Try to run a 'vacuum full;' and re-run your query. And, run a 'explain analyse <your query>' to see the estimated costs and the real costs. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes: > I guess, you have much insert/delete or update - operations on this > table and no recent vacuum. > > Try to run a 'vacuum full;' and re-run your query. And, run a 'explain > analyse <your query>' to see the estimated costs and the real costs. Btw, run 'select relpages, reltuples from pg_class where relname='phone'; before and after the 'vacuum full' and show us the result. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer schreef: > am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes: >> I guess, you have much insert/delete or update - operations on this >> table and no recent vacuum. >> >> Try to run a 'vacuum full;' and re-run your query. And, run a 'explain >> analyse <your query>' to see the estimated costs and the real costs. > > Btw, run 'select relpages, reltuples from pg_class where relname='phone'; > before and after the 'vacuum full' and show us the result. > > > Andreas Andreas, thanks for answering. I'll run the vacuum full tonight, I don't dare running it during the day, and I'll show you the query outputs as well. Thanks, Ron -- NeoNova BV, The Netherlands Professional internet and VoIP solutions http://www.neonova.nl Kruislaan 419 1098 VA Amsterdam info: 020-5628292 servicedesk: 020-5628292 fax: 020-5628291 KvK Amsterdam 34151241 The following disclaimer applies to this email: http://www.neonova.nl/maildisclaimer
A. Kretschmer schreef:
> am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes:
>> I guess, you have much insert/delete or update - operations on this
>> table and no recent vacuum.
>>
>> Try to run a 'vacuum full;' and re-run your query. And, run a 'explain
>> analyse <your query>' to see the estimated costs and the real costs.
>
> Btw, run 'select relpages, reltuples from pg_class where relname='phone';
> before and after the 'vacuum full' and show us the result.
>
>
> Andreas
I did a vacuum without success, but `vacuum full` did the trick:
tium=# select relpages, reltuples from pg_class where relname='phone';
relpages | reltuples
----------+-----------
85876 | 658
(1 row)
tium=# vacuum full;
VACUUM
tium=# select relpages, reltuples from pg_class where relname='phone';
relpages | reltuples
----------+-----------
17 | 248
(1 row)
tium=# explain analyze select codec1, phonetype from phone;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on phone (cost=0.00..76.77 rows=977 width=11) (actual time=0.007..0.751 rows=248 loops=1)
Total runtime: 0.957 ms
(2 rows)
tium=#
What do those values relpages and reltuples mean?
Thanks,
Ron
--
NeoNova BV, The Netherlands
Professional internet and VoIP solutions
http://www.neonova.nl Kruislaan 419 1098 VA Amsterdam
info: 020-5628292 servicedesk: 020-5628292 fax: 020-5628291
KvK Amsterdam 34151241
The following disclaimer applies to this email:
http://www.neonova.nl/maildisclaimer
am Tue, dem 03.06.2008, um 19:51:05 +0200 mailte Ron Arts folgendes: > A. Kretschmer schreef: > >am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes: > >>I guess, you have much insert/delete or update - operations on this > >>table and no recent vacuum. > >> > >>Try to run a 'vacuum full;' and re-run your query. And, run a 'explain > >>analyse <your query>' to see the estimated costs and the real costs. > > > >Btw, run 'select relpages, reltuples from pg_class where relname='phone'; > >before and after the 'vacuum full' and show us the result. > What do those values relpages and reltuples mean? relpages: number of physical pages for the table on disk (one page=4 Kbyte) reltuples: number of tuples for the table Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Tue, dem 03.06.2008, um 19:51:05 +0200 mailte Ron Arts folgendes: > A. Kretschmer schreef: > >am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes: > >>I guess, you have much insert/delete or update - operations on this > >>table and no recent vacuum. > >> > >>Try to run a 'vacuum full;' and re-run your query. And, run a 'explain > >>analyse <your query>' to see the estimated costs and the real costs. > > > >Btw, run 'select relpages, reltuples from pg_class where relname='phone'; > >before and after the 'vacuum full' and show us the result. > > > > > >Andreas > > I did a vacuum without success, but `vacuum full` did the trick: vacuum: tags dead tuples for reuse vacuum full: recover the space occopied by dead tuples Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer wrote: > am Tue, dem 03.06.2008, um 19:51:05 +0200 mailte Ron Arts folgendes: >> A. Kretschmer schreef: >>> am Tue, dem 03.06.2008, um 12:35:34 +0200 mailte A. Kretschmer folgendes: >>>> I guess, you have much insert/delete or update - operations on this >>>> table and no recent vacuum. >>>> >>>> Try to run a 'vacuum full;' and re-run your query. And, run a 'explain >>>> analyse <your query>' to see the estimated costs and the real costs. >>> Btw, run 'select relpages, reltuples from pg_class where relname='phone'; >>> before and after the 'vacuum full' and show us the result. >>> >>> >>> Andreas >> I did a vacuum without success, but `vacuum full` did the trick: > > vacuum: tags dead tuples for reuse > vacuum full: recover the space occopied by dead tuples > > > Andreas The docs state (explaining that VACUUM FULL is unnecessary because it only returns diskspace to the OS) > The standard form of VACUUM is best used with the goal of maintaining a fairly level steady-state usage of disk space.If you need to return > disk space to the operating system you can use VACUUM FULL — but what's the point of releasing disk space that will onlyhave to be allocated > again soon? But it seems there is a very valid reason for running VACUUM FULL once in a while: when records are frequently updated, but seldomly added/deleted to the table. Otherwise the number of stale records keep growing, and so will the time to scan the entire table. And the sentence: > VACUUM FULL is recommended for cases where you know you have deleted the majority of rows in a table should also include 'or updated the same records many times'. Right? Ron
Attachment
am Tue, dem 03.06.2008, um 22:00:31 +0200 mailte Ron Arts folgendes: > And the sentence: > > >VACUUM FULL is recommended for cases where you know you have deleted the > >majority of rows in a table > > should also include 'or updated the same records many times'. Right? Yes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
A. Kretschmer schreef: > am Tue, dem 03.06.2008, um 22:00:31 +0200 mailte Ron Arts folgendes: > >> And the sentence: >> >>> VACUUM FULL is recommended for cases where you know you have deleted the >>> majority of rows in a table >> should also include 'or updated the same records many times'. Right? > > Yes. > > Andreas Andreas, thank you very much for increasing my understanding, I really appreciate it. Ron -- NeoNova BV, The Netherlands Professional internet and VoIP solutions http://www.neonova.nl Kruislaan 419 1098 VA Amsterdam info: 020-5628292 servicedesk: 020-5628292 fax: 020-5628291 KvK Amsterdam 34151241 The following disclaimer applies to this email: http://www.neonova.nl/maildisclaimer