Re: [HACKERS] subselect and optimizer
От | Bruce Momjian |
---|---|
Тема | Re: [HACKERS] subselect and optimizer |
Дата | |
Msg-id | 199804121411.KAA22557@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: [HACKERS] subselect and optimizer ("Boersenspielteam" <boersenspiel@vocalweb.de>) |
Ответы |
Re: [HACKERS] subselect and optimizer
("Boersenspielteam" <boersenspiel@vocalweb.de>)
|
Список | pgsql-hackers |
> Yep, exactly. The query with the where clause is fixed after > applying Vadim's prune.c patch, simple join still uses two seq scans > :-( > > I uploaded test data and Vadim fixed one file, but asked you > (Bruce) to look over other files of the optimizer code. There seem > to be other bugs in the optimizer code, which were introduced between > 6.2.1 and 6.3. We have seen about 5-6 error reports from different > people, from the simpliest queries like my simple join to rather > complex subqueries. But when a simple join doesn't work (ok, it > works, but kind of crawls), this error is supposed to pop up under > other circumstances too. > > Hope you can find this nasty little bug, cause it makes postgres > unusable. Especially before going into development again. > > See the mailinglist archives for a post of mine. There is a link in > it,where you can download the test data, it should still be > there. (don't have access to this from home) > > I greatly appreciate all the time and hard work all you > PostgreSQL-hackers and contributors put into this fantastic freeware > product. Just to let you know. Here is the prune.c file from 6.2.1. Please try it and let me know if it fixes the problem: --------------------------------------------------------------------------- /*------------------------------------------------------------------------- * * prune.c-- * Routines to prune redundant paths and relations * * Copyright (c) 1994, Regents of the University of California * * * IDENTIFICATION * $Header: /usr/local/cvsroot/pgsql/src/backend/optimizer/path/prune.c,v 1.6 1997/09/08 21:45:08 momjian Exp $ * *------------------------------------------------------------------------- */ #include "postgres.h" #include "nodes/pg_list.h" #include "nodes/relation.h" #include "optimizer/internal.h" #include "optimizer/cost.h" #include "optimizer/paths.h" #include "optimizer/pathnode.h" #include "utils/elog.h" static List *prune_joinrel(Rel *rel, List *other_rels); /* * prune-joinrels-- * Removes any redundant relation entries from a list of rel nodes * 'rel-list'. * * Returns the resulting list. * */ List * prune_joinrels(List *rel_list) { List *temp_list = NIL; if (rel_list != NIL) { temp_list = lcons(lfirst(rel_list), prune_joinrels(prune_joinrel((Rel *) lfirst(rel_list), lnext(rel_list)))); } return (temp_list); } /* * prune-joinrel-- * Prunes those relations from 'other-rels' that are redundant with * 'rel'. A relation is redundant if it is built up of the same * relations as 'rel'. Paths for the redundant relation are merged into * the pathlist of 'rel'. * * Returns a list of non-redundant relations, and sets the pathlist field * of 'rel' appropriately. * */ static List * prune_joinrel(Rel *rel, List *other_rels) { List *i = NIL; List *t_list = NIL; List *temp_node = NIL; Rel *other_rel = (Rel *) NULL; foreach(i, other_rels) { other_rel = (Rel *) lfirst(i); if (same(rel->relids, other_rel->relids)) { rel->pathlist = add_pathlist(rel, rel->pathlist, other_rel->pathlist); t_list = nconc(t_list, NIL); /* XXX is this right ? */ } else { temp_node = lcons(other_rel, NIL); t_list = nconc(t_list, temp_node); } } return (t_list); } /* * prune-rel-paths-- * For each relation entry in 'rel-list' (which corresponds to a join * relation), set pointers to the unordered path and cheapest paths * (if the unordered path isn't the cheapest, it is pruned), and * reset the relation's size field to reflect the join. * * Returns nothing of interest. * */ void prune_rel_paths(List *rel_list) { List *x = NIL; List *y = NIL; Path *path = NULL; Rel *rel = (Rel *) NULL; JoinPath *cheapest = (JoinPath *) NULL; foreach(x, rel_list) { rel = (Rel *) lfirst(x); rel->size = 0; foreach(y, rel->pathlist) { path = (Path *) lfirst(y); if (!path->p_ordering.ord.sortop) { break; } } cheapest = (JoinPath *) prune_rel_path(rel, path); if (IsA_JoinPath(cheapest)) { rel->size = compute_joinrel_size(cheapest); } else elog(WARN, "non JoinPath called"); } } /* * prune-rel-path-- * Compares the unordered path for a relation with the cheapest path. If * the unordered path is not cheapest, it is pruned. * * Resets the pointers in 'rel' for unordered and cheapest paths. * * Returns the cheapest path. * */ Path * prune_rel_path(Rel *rel, Path *unorderedpath) { Path *cheapest = set_cheapest(rel, rel->pathlist); /* don't prune if not pruneable -- JMH, 11/23/92 */ if (unorderedpath != cheapest && rel->pruneable) { rel->unorderedpath = (Path *) NULL; rel->pathlist = lremove(unorderedpath, rel->pathlist); } else { rel->unorderedpath = (Path *) unorderedpath; } return (cheapest); } /* * merge-joinrels-- * Given two lists of rel nodes that are already * pruned, merge them into one pruned rel node list * * 'rel-list1' and * 'rel-list2' are the rel node lists * * Returns one pruned rel node list */ List * merge_joinrels(List *rel_list1, List *rel_list2) { List *xrel = NIL; foreach(xrel, rel_list1) { Rel *rel = (Rel *) lfirst(xrel); rel_list2 = prune_joinrel(rel, rel_list2); } return (append(rel_list1, rel_list2)); } /* * prune_oldrels-- * If all the joininfo's in a rel node are inactive, * that means that this node has been joined into * other nodes in all possible ways, therefore * this node can be discarded. If not, it will cause * extra complexity of the optimizer. * * old_rels is a list of rel nodes * * Returns a new list of rel nodes */ List * prune_oldrels(List *old_rels) { Rel *rel; List *joininfo_list, *xjoininfo; if (old_rels == NIL) return (NIL); rel = (Rel *) lfirst(old_rels); joininfo_list = rel->joininfo; if (joininfo_list == NIL) return (lcons(rel, prune_oldrels(lnext(old_rels)))); foreach(xjoininfo, joininfo_list) { JInfo *joininfo = (JInfo *) lfirst(xjoininfo); if (!joininfo->inactive) return (lcons(rel, prune_oldrels(lnext(old_rels)))); } return (prune_oldrels(lnext(old_rels))); } -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
В списке pgsql-hackers по дате отправления: