Обсуждение: Except operation

Hi
The query:
select * from webdata except select * from webdata1;
 takes abysmally long .How can I optimise it?
The particulars are:

tracedb=> \d webdata
Table    = webdata
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| tid                              | int4                             |
4 |
| itemid                           | int4                             |
4 |
| ordering                         | int4                             |
4 |
+----------------------------------+----------------------------------+-------+

tracedb=> \d webdata1
Table    = webdata1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| tid                              | int4                             |
4 |
| itemid                           | int4                             |
4 |
| ordering                         | int4                             |
4 |
+----------------------------------+----------------------------------+-------+
tracedb=> select count(*) from webdata;
count
-----
91155
(1 row)

tracedb=> select count(*) from webdata1;
count
-----
 9550
(1 row)

Regards
Satya

Satyajeet Seth wrote:
>
> Hi
> The query:
> select * from webdata except select * from webdata1;
>  takes abysmally long .How can I optimise it?
> The particulars are:

You could try

  select * from webdata w
  where not exists
    (select * from webdata1 w1
     where w1.tid=w.tid
      ...
        )

If you have the correct indexes on webdata1 this can be quite fast.

Adriaan

how about I'm a sql92 lover? :-)

the idea is to use index. but the engine only use index when it sees
"where". so, how about add "where w.tid = w.tid" to the first (or
both) select on the two sides of the except?

not tested. if work, please let us know, thanks.

Kai
On Thu, 2 Dec 1999, Adriaan Joubert wrote:

> Satyajeet Seth wrote:
> >
> > Hi
> > The query:
> > select * from webdata except select * from webdata1;
> >  takes abysmally long .How can I optimise it?
> > The particulars are:
>
> You could try
>
>   select * from webdata w
>   where not exists
>     (select * from webdata1 w1
>      where w1.tid=w.tid
>       ...
>         )
>
> If you have the correct indexes on webdata1 this can be quite fast.
>
> Adriaan
>
> ************
>

kaiq@realtyideas.com wrote:

> how about I'm a sql92 lover? :-)
>
> the idea is to use index. but the engine only use index when it sees
> "where". so, how about add "where w.tid = w.tid" to the first (or
> both) select on the two sides of the except?
>
> not tested. if work, please let us know, thanks.
>
> Kai

That's not the problem. The problem is that the INTERSECT/EXCEPT code
uses the query rewriter to automatically rewrite the query to something
like:

SELECT * FROM webdata w
WHERE (<webdata.fieldlist>) NOT IN (
    SELECT * FROM webdata1);

and 'IN' clauses in PostgreSQL can't use indices.  The result is always a
sequential scan on the IN values. INTERSECT/EXCEPT should have been
written to rewrite the query using correlated subqueries and the EXISTS
test, as Adriaan suggests below. In fact, one of the possible solutions
for the un-usability of IN clauses is to have PostgreSQL rewrite those as
EXISTS....but no one's done that yet.

Mike

>
> On Thu, 2 Dec 1999, Adriaan Joubert wrote:
>
> > Satyajeet Seth wrote:
> > >
> > > Hi
> > > The query:
> > > select * from webdata except select * from webdata1;
> > >  takes abysmally long .How can I optimise it?
> > > The particulars are:
> >
> > You could try
> >
> >   select * from webdata w
> >   where not exists
> >       (select * from webdata1 w1
> >        where w1.tid=w.tid
> >         ...
> >         )
> >
> > If you have the correct indexes on webdata1 this can be quite fast.
> >
> > Adriaan