Обсуждение: ANTI-JOIN needs table, index scan not possible?
Hi, I need an ANTI-JOIN (not exists SELECT something from table.../ left join table WHERE table.id IS NULL) on the same table.Acutally I have an index to serve the not exists question, but the query planner chooses to to a bitmap heap scan. The table has 100 Mio rows, so doing a heap scan is messed up... It would be really fast if Postgres could compare the to indicies. Does Postgres have to visit the table for this ANTI-JOIN? I know the table has to be visitied at some point to serve the MVCC, but why so early? Can NOT ESISTS only be fixed by thetable, because it could miss soemthing otherwise? -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de
> I know the table has to be visitied at some point to serve the MVCC, but why so early? Can NOT ESISTS only be fixed bythe table, because it could miss soemthing otherwise? Possibly because the index entries you're anti-joining against may point to deleted tuples, so you would erroneously omit rows from the join result if you skip the visibility check? --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
Thanks for the answer. so there's no way around this problem? A nice index bitmap merge thing would be super fast. Big table ANTI JOIN queries withonly a few results expected, are totally broken, if this is true. This way the query breaks my neck. This is a massive downside of postgres which makes this kind of query impossible. Mysqlgives you the answer in a few seconds :-( > Possibly because the index entries you're anti-joining against may > point to deleted tuples, so you would erroneously omit rows from the > join result if you skip the visibility check? > > --- > Maciek Sakrejda | System Architect | Truviso > > 1065 E. Hillsdale Blvd., Suite 215 > Foster City, CA 94404 > (650) 242-3500 Main > www.truviso.com -- Schon gehört? GMX hat einen genialen Phishing-Filter in die Toolbar eingebaut! http://www.gmx.net/de/go/toolbar
On Fri, Mar 11, 2011 at 06:54:39PM +0100, hans wulf wrote: > Thanks for the answer. > > so there's no way around this problem? A nice index bitmap merge thing would be super fast. Big table ANTI JOIN querieswith only a few results expected, are totally broken, if this is true. > > This way the query breaks my neck. This is a massive downside of postgres which makes this kind of query impossible. Mysqlgives you the answer in a few seconds :-( > > Super! I am glad that MySQL can meet your needs. No software is perfect and you should definitely chose based on your use-case. Regards, Ken
Kenneth Marshall <ktm@rice.edu> wrote: > On Fri, Mar 11, 2011 at 06:54:39PM +0100, hans wulf wrote: >> so there's no way around this problem? A nice index bitmap merge >> thing would be super fast. Big table ANTI JOIN queries with only >> a few results expected, are totally broken, if this is true. >> >> This way the query breaks my neck. This is a massive downside of >> postgres which makes this kind of query impossible. Mysql gives >> you the answer in a few seconds :-( > > Super! I am glad that MySQL can meet your needs. No software is > perfect and you should definitely chose based on your use-case. Well, as far as I can see we haven't yet seen near enough information to diagnose the issue, suggest alternative ways to write the query which might perform better, or determine whether there's an opportunity to improve the optimizer here. Hans, please read this page and provide more detail: http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin
On Fri, Mar 11, 2011 at 10:32 AM, hans wulf <lotu1@gmx.net> wrote: > I need an ANTI-JOIN (not exists SELECT something from table.../ left join table WHERE table.id IS NULL) on the same table.Acutally I have an index to serve the not exists question, but the query planner chooses to to a bitmap heap scan. > > The table has 100 Mio rows, so doing a heap scan is messed up... > > It would be really fast if Postgres could compare the to indicies. Does Postgres have to visit the table for this ANTI-JOIN? A bitmap heap scan implies that a bitmap index scan is also being done, so it IS using the indexes. Now that leaves open the question of why it's not fast... but it's hard to guess the answer to that question without seeing at least the EXPLAIN output, preferably EXPLAIN ANALYZE. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company