Re: PG 9.1 much slower than 8.2 ? - Mailing list pgsql-novice

Hi Thomas and Keith,

After I have re-tested the performance after a plain insert of the dump
without any vacuuming or reindexing, I just have executed the following
on both PostgreSQL versions. I know, REINDEXING after a VACUUM FULL is
only suggested with pre-9.0 versions, but I haven't heard of any harm
doing it with >=9.0 versions, either, so I executed them there as well:

a) VACUUM FULL:
for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
-c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
expand | sed 's# \+##g'); do
   /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "VACUUM FULL
VERBOSE ${table};" db
done

b) REINDEX TABLE:
for table in $(/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 db
-c "\dt" | tail -n +4 | head -n -2 | cut -d '|' -f 2 | sed 's#^ ##g' |
expand | sed 's# \+##g'); do
   /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
TABLE ${table};" db
done

c) REINDEX DATABASE:
/usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c "REINDEX
DATABASE db;" db

I did all these commands on the 9.3 Postgres as well by replacing Port
5433 with 5432 and /usr/local/bin/psql with /usr/bin/psql .

After this, I re-issued the test to selecting a 360881 rowed table again
without getting different results:

for x in 1 2 3 ; do
   sync
   sleep 10
   time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \
   "SELECT * FROM billing_events;" db | wc -l
done

Again, I used Port 5432 instead of 5433 and /usr/bin/psql instead of
/usr/local/bin/psql for testing the 9.3 Postgres:

8.2:
360881
real    0m5.996s
user    0m4.448s
sys     0m0.724s

360881
real    0m6.023s
user    0m4.520s
sys     0m0.664s

360881
real    0m6.077s
user    0m4.580s
sys     0m0.664s


9.3:
360881
real    0m12.835s
user    0m9.737s
sys     0m0.708s

360881
real    0m12.689s
user    0m9.685s
sys     0m0.652s

360881
real    0m12.700s
user    0m9.649s
sys     0m0.700s

After this, I ran "ANALYSE;" while connected to the DB "db" without any
further arguments, as Keith suggested. It echoed nothing but "ANALYZE"
after a few seconds on both psql shells.
After this, I ran the "SELECT *" again, identically with to what is
described above.
The result is still the same: 9.3 needs twice the time of 8.2 to return
the results.

As you asked for, here comes the "EXPLAIN ANALYSE" output for 8.2:

             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
  Seq Scan on billing_events  (cost=0.00..16098.40 rows=360940
width=316) (actual time=0.015..84.507 rows=360877 loops=1)
  Total runtime: 114.922 ms
(2 rows)


... followed by 9.3 output for "EXPLAIN ANALYSE":

             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
  Seq Scan on billing_events  (cost=0.00..15409.77 rows=360877
width=302) (actual time=0.035..97.698 rows=360877 loops=1)
  Total runtime: 128.252 ms
(2 Zeilen)

... followed by 9.3 output for "EXPLAIN (analyze true, verbose true,
buffers true) select ...":

             QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on public.billing_events  (cost=0.00..15409.77 rows=360877
width=302) (actual time=0.033..96.809 rows=360877 loops=1)
    Output: id, callid, name, type, callingnumber, callednumber,
translatednumber, inserted, eventstart, duration, freeofchargeflag,
eventdata, envoxid, cpc, taskid
    Buffers: shared hit=11801
  Total runtime: 130.506 ms
(4 Zeilen)

These don't me tell anything. Do they help you understanding this issue?

Best regards,
Marc

Am 27.08.2014 17:00, schrieb Keith:
>
>
>
> On Wed, Aug 27, 2014 at 2:55 AM, Thomas Kellerer <spam_eater@gmx.net
> <mailto:spam_eater@gmx.net>> wrote:
>
>     Marc Richter schrieb am 26.08.2014 um 17:10:
>      > I've managed to create a dump of the database from 8.2.5 and
>      > inserting it into 9.1.13 successfully, thanks to the help of this
>      > list ("Upgrading from PG 8.2.5 to 9.1.13" - Thread). So I gave the
>      > result to another department to make their compatibility- and
>      > overall-tests on it. They did not come up with incompatibilities, but
>      > with a performance-related issue:
>      >
>      > When we do a "SELECT *" on a table with 355332 rows in it without
>      > using an index or limit or such, this takes round about 10.5 seconds
>      > on the PostgreSQL 8.2.5 host and 12.2 seconds on the PostgreSQL
>      > 9.1.13 host. Both servers are using the same database.
>
>     Can you share the output of explain analyze for both servers?
>
>     (for 9.1 maybe even "explain (analyze true, verbose true, buffers
>     true) select ...")
>
>     Also: try to run a "vacuum full" on the 9.1 database - just to make sure
>
>     Thomas
>
>
>
>     --
>     Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
>     <mailto:pgsql-novice@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-novice
>
>
> Actually, what may be more important than a vacuum full would be a full
> database analyze.
> Just run "ANALYZE" while logged into your database via psql. With no
> tables given to the command, it should just analyze the whole thing.
> This should update the planner statistics which are probably empty after
> a full dump/restore.