Re: [HACKERS] having and union in v7beta

From: Jose Soares <jose(at)sferacarta(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] having and union in v7beta
Date: 2000-02-29 08:34:59
Message-ID: 38BB84B3.1209A0C7@sferacarta.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane wrote:

> 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?

6.5 takes 0.463s
7.0 takes 1.640s
the field type is CHAR(4)

>
>
> 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
>

I'm running both of them in this way:
postmaster -i -o -F -B 512 -S > server.log 2>&1

> 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

--
Jose' Soares
Bologna, Italy Jose(at)sferacarta(dot)com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2000-02-29 10:17:05 Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
Previous Message Jose Soares 2000-02-29 08:07:11 Re: [HACKERS] having and union in v7beta