Re: PG 9.1 much slower than 8.2 ?

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(at)gmx(dot)net
> <mailto:spam_eater(at)gmx(dot)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(at)postgresql(dot)org
> <mailto:pgsql-novice(at)postgresql(dot)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.