Date: Thu, 23 Jun 2005 11:09:45 -0400
From: Elliott Bennett <pgmail@dhtns.com>
To: pgsql-performance@postgresql.org
Subject: select distinct on varchar - wild performance differences!
Message-ID: <20050623150945.GA31186@turing.dhtns.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.6+20040907i

Hey, all.  I've bounced this around in #postgres for an hour or so, and
it was suggested that I post it here as well.  Hopefully someone can
help me out.

I have three machines.  All have 512MB of ram.
Machine A is a 2.0ghz celeron, running debian, pg verison 7.4.6.
Machine B is a 1.8ghz celeron, running centos 3.4, pg verison 8.0.3.
(7.3.9 also exhibited the behaviour below, by the way)
Machine C is a 1.0ghz athlon, running centos 4.0,  pg verison 7.4.7.


The SAME data and schema is loaded (from a pg_dump, default parameters)
onto all three machines.   With the same query: "select distinct model
from exif_common", machines A and C return results quickly (1/4
second).  Machine B chews on it for 30ish seconds!  Note, this column is
a VARCHAR(40).

Here's an explain analyze for it.

Machine A (fast):
photos=# explain analyze select distinct model from exif_common;
                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
+--
Unique  (cost=2629.74..2732.11 rows=5 width=15) (actual
time=211.358..265.049 rows=6 loops=1)
  ->  Sort  (cost=2629.74..2680.93 rows=20473 width=15) (actual
  time=211.351..242.296 rows=20473 loops=1)
        Sort Key: model
        ->  Seq Scan on exif_common  (cost=0.00..1163.73 rows=20473
        width=15) (actual time=0.022..58.635 rows=20473 loops=1)
Total runtime: 265.928 ms
(5 rows)


Machine B (slow):
photos=# explain analyze select distinct model from exif_common;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
+--
Unique  (cost=2640.74..2743.11 rows=6 width=15) (actual
time=27939.231..32914.134 rows=6 loops=1)
  ->  Sort  (cost=2640.74..2691.93 rows=20473 width=15) (actual
  time=27939.222..27983.784 rows=20473 loops=1)
        Sort Key: model
        ->  Seq Scan on exif_common  (cost=0.00..1174.73 rows=20473
        width=15) (actual time=0.071..97.772 rows=20473 loops=1)
Total runtime: 32915.031 ms
(5 rows)


( yes, i know, six distinct rows out of 20,000....   But holy moly!  1/4
sec vs 32.9 sec?!?! )


Now, if I do a similar query against an INT column, the speeds are more
in line with each other:

Machine A:
photos=# explain analyze select distinct imagewidth from exif_common;

                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
+-
Unique  (cost=2629.74..2732.11 rows=36 width=4) (actual
time=179.899..225.934 rows=107 loops=1)
  ->  Sort  (cost=2629.74..2680.93 rows=20473 width=4) (actual
  time=179.891..207.632 rows=20473 loops=1)
        Sort Key: imagewidth
        ->  Seq Scan on exif_common  (cost=0.00..1163.73 rows=20473
width=4)
        (actual time=0.024..62.946 rows=20473 loops=1)
Total runtime: 226.707 ms
(5 rows)


Machine B:
photos=# explain analyze select distinct imagewidth from exif_common;
                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
+-
Unique  (cost=2640.74..2743.11 rows=24 width=4) (actual
time=209.394..287.131 rows=107 loops=1)
  ->  Sort  (cost=2640.74..2691.93 rows=20473 width=4) (actual
  time=209.384..251.693 rows=20473 loops=1)
        Sort Key: imagewidth
        ->  Seq Scan on exif_common  (cost=0.00..1174.73 rows=20473
width=4)
        (actual time=0.074..94.574 rows=20473 loops=1)
Total runtime: 288.411 ms

(5 rows)


Machine C exhibits the same behaviour as A for all queries.

This weird slow behaviour on machine B also appeared in 7.3.9.
Upgrading didn't seem to help.

neilc from irc thought it may be a qsort(2) quirk, but a sample C
program I whipped up testing different sized data sets with a similar
distribution gave very similar sort timings between the three
machines..  Therefore, I don't think it's qsort(2) to blame...

Anyone have any ideas as to what may be up with machine B?

Thanks,
-Elliott