Thread: BUG #5542: Query optimization problem

The following bug has been logged online:

Bug reference:      5542
Logged by:          Roman
Email address:      zotov@oe-it.ru
PostgreSQL version: 9.0
Operating system:   Win7 x64
Description:        Query optimization problem
Details:

I have a query:

SELECT d1.ID, d2.ID
FROM DocPrimary d1
  JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
WHERE (d1.ID=234409763) or (d2.ID=234409763)

i think what QO(Query Optimizer) can make it faster (now it seq scan and on
million records works 7 sec)

SELECT d1.ID, d2.ID
FROM DocPrimary d1
  JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
WHERE (d2.BasedOn=234409763) or (d2.ID=234409763)

Result of this query are identical (and can get by replace part of where use
join dependence)
And it works 0-10ms

I think, what QO can undertstand it self.

Table create query:

CREATE TABLE docprimary
(
  id integer NOT NULL,
  basedon integer,
  CONSTRAINT id_pk PRIMARY KEY (id)
);

CREATE INDEX basedon_idx
  ON docprimary
  USING btree
  (basedon);

On 06/07/10 14:43, Roman wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5542
> Logged by:          Roman
> Email address:      zotov@oe-it.ru
> PostgreSQL version: 9.0
> Operating system:   Win7 x64
> Description:        Query optimization problem
> Details:

On the bug report form, you will have seen a note telling you that this
form is only for bug reports. If you want help with optimization,
contact the pgsql-performance mailing list.

Are you actually reporting an issue with PostgreSQL's behavior that's
unclear from your post? Or are you just asking for help with a query?

--
Craig Ringer

On Tue, Jul 6, 2010 at 2:43 AM, Roman <zotov@oe-it.ru> wrote:
> I have a query:
>
> SELECT d1.ID, d2.ID
> FROM DocPrimary d1
> =A0JOIN DocPrimary d2 ON d2.BasedOn=3Dd1.ID
> WHERE (d1.ID=3D234409763) or (d2.ID=3D234409763)
>
> i think what QO(Query Optimizer) can make it faster (now it seq scan and =
on
> million records works 7 sec)
>
> SELECT d1.ID, d2.ID
> FROM DocPrimary d1
> =A0JOIN DocPrimary d2 ON d2.BasedOn=3Dd1.ID
> WHERE (d2.BasedOn=3D234409763) or (d2.ID=3D234409763)

EXPLAIN ANALYZE output for both queries, please?

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company