From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jose Soares <jose(at)sferacarta(dot)com> |
Cc: | hackers <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] having and union in v7beta |
Date: | 2000-02-29 00:16:15 |
Message-ID: | 27055.951783375@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Jose Soares <jose(at)sferacarta(dot)com> writes:
>>>> SELECT ... UNION (is 3 / 4 times slow)
>>
>> Can't help you on that without more details, either. What is the
>> query exactly, what plan does EXPLAIN show, and what plan did you
>> get from 6.5?
> psql7=> EXPLAIN select distretto from comuni union select codice_fiscale from comuni;
> NOTICE: QUERY PLAN:
> Unique (cost=1767.19..1808.90 rows=1668 width=12)
-> Sort (cost=1767.19..1767.19 rows=16684 width=12)
-> Append (cost=0.00..464.84 rows=16684 width=12)
-> Seq Scan on comuni (cost=0.00..232.42 rows=8342 width=12)
-> Seq Scan on comuni (cost=0.00..232.42 rows=8342 width=12)
> [ and exactly the same plan for 6.5 ]
OK, so much for my first thought that the 7.0 planner was choosing a
bad plan.
One relevant change is that Unique nodes now invoke the proper
type-specific equality function(s) to decide whether tuples are distinct
or not, instead of doing a bitwise comparison (memcmp()) like they did
before. But it's tough to believe that that accounts for a 3-to-4x
slowdown of this query; certainly I don't see much performance
difference on the datatypes I tried. What datatypes are your fields,
anyway?
The other possibility is that the Sort step is a lot slower in 7.0,
although I don't think it should be. Are you running both versions
with the same -S setting, and if so what is it? Could it be that
the query is right on the edge of needing to switch from memory-based
to disk-based sort? Perhaps 7.0 is deciding that it needs to go to
disk a little sooner than 6.5 did.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2000-02-29 00:28:11 | Re: NOT {NULL|DEFERRABLE} (was: bug in 7.0) |
Previous Message | Hannu Krosing | 2000-02-29 00:04:56 | Re: [HACKERS] Re: ALTER TABLE DROP COLUMN |