Обсуждение: 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