Lists: | pgsql-bugs |
---|
From: | "Roman" <zotov(at)oe-it(dot)ru> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5542: Query optimization problem |
Date: | 2010-07-06 06:43:47 |
Message-ID: | 201007060643.o666hlPa017398@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5542
Logged by: Roman
Email address: zotov(at)oe-it(dot)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);
From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Roman <zotov(at)oe-it(dot)ru> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5542: Query optimization problem |
Date: | 2010-07-07 01:34:51 |
Message-ID: | 4C33D9BB.1010603@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 06/07/10 14:43, Roman wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5542
> Logged by: Roman
> Email address: zotov(at)oe-it(dot)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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Roman <zotov(at)oe-it(dot)ru> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5542: Query optimization problem |
Date: | 2010-07-09 02:29:23 |
Message-ID: | AANLkTilrRoFkqoUyPW2A50HyQ2fydOG_zoH3cbCoYdB8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Tue, Jul 6, 2010 at 2:43 AM, Roman <zotov(at)oe-it(dot)ru> wrote:
> 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)
EXPLAIN ANALYZE output for both queries, please?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company