pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedANTI-JOIN needs table, index scan not possible?
6+ messages / 5 participants
[nested] [flat]
* ANTI-JOIN needs table, index scan not possible?
@ 2011-03-11 15:32 hans wulf <lotu1@gmx.net>
2011-03-11 17:18 ` Re: ANTI-JOIN needs table, index scan not possible? Maciek Sakrejda <msakrejda@truviso.com>
2011-03-23 04:31 ` Re: ANTI-JOIN needs table, index scan not possible? Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: hans wulf @ 2011-03-11 15:32 UTC (permalink / raw)
To: pgsql-performance
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 the table, 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ANTI-JOIN needs table, index scan not possible?
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
@ 2011-03-11 17:18 ` Maciek Sakrejda <msakrejda@truviso.com>
2011-03-11 17:54 ` Re: ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
1 sibling, 1 reply; 6+ messages in thread
From: Maciek Sakrejda @ 2011-03-11 17:18 UTC (permalink / raw)
To: hans wulf <lotu1@gmx.net>; +Cc: pgsql-performance
> 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 the 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ANTI-JOIN needs table, index scan not possible?
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
2011-03-11 17:18 ` Re: ANTI-JOIN needs table, index scan not possible? Maciek Sakrejda <msakrejda@truviso.com>
@ 2011-03-11 17:54 ` hans wulf <lotu1@gmx.net>
2011-03-11 22:16 ` Re: ANTI-JOIN needs table, index scan not possible? Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 6+ messages in thread
From: hans wulf @ 2011-03-11 17:54 UTC (permalink / raw)
To: Maciek Sakrejda <msakrejda@truviso.com>; +Cc: pgsql-performance
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 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 :-(
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ANTI-JOIN needs table, index scan not possible?
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
2011-03-11 17:18 ` Re: ANTI-JOIN needs table, index scan not possible? Maciek Sakrejda <msakrejda@truviso.com>
2011-03-11 17:54 ` Re: ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
@ 2011-03-11 22:16 ` Kenneth Marshall <ktm@rice.edu>
2011-03-11 22:32 ` Re: ANTI-JOIN needs table, index scan not possible? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Kenneth Marshall @ 2011-03-11 22:16 UTC (permalink / raw)
To: hans wulf <lotu1@gmx.net>; +Cc: Maciek Sakrejda <msakrejda@truviso.com>; pgsql-performance
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 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.
Regards,
Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ANTI-JOIN needs table, index scan not possible?
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
2011-03-11 17:18 ` Re: ANTI-JOIN needs table, index scan not possible? Maciek Sakrejda <msakrejda@truviso.com>
2011-03-11 17:54 ` Re: ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
2011-03-11 22:16 ` Re: ANTI-JOIN needs table, index scan not possible? Kenneth Marshall <ktm@rice.edu>
@ 2011-03-11 22:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2011-03-11 22:32 UTC (permalink / raw)
To: hans wulf <lotu1@gmx.net>; Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-performance; Maciek Sakrejda <msakrejda@truviso.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ANTI-JOIN needs table, index scan not possible?
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
@ 2011-03-23 04:31 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Robert Haas @ 2011-03-23 04:31 UTC (permalink / raw)
To: hans wulf <lotu1@gmx.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-03-23 04:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-11 15:32 ANTI-JOIN needs table, index scan not possible? hans wulf <lotu1@gmx.net>
2011-03-11 17:18 ` Maciek Sakrejda <msakrejda@truviso.com>
2011-03-11 17:54 ` hans wulf <lotu1@gmx.net>
2011-03-11 22:16 ` Kenneth Marshall <ktm@rice.edu>
2011-03-11 22:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-23 04:31 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox