Optimising queries involving unions

От: Sam Mason
Тема: Optimising queries involving unions
Дата: ,
Msg-id: 20050526152203.GI15205@colo.samason.me.uk
(см: обсуждение, исходный текст)
Ответы: Re: Optimising queries involving unions  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Optimising queries involving unions  (Sam Mason, )
 Re: Optimising queries involving unions  (Tom Lane, )
  Re: Optimising queries involving unions  (Sam Mason, )
   Re: Optimising queries involving unions  (Tom Lane, )
 Re: Optimising queries involving unions  ("Marc Mamin", )

Hi,

I've got a query that I think the query optimiser should be able
to work it's magic on but it doesn't!  I've had a look around and
asked on the IRC channel and found that the current code doesn't
attempt to optimise for what I'm asking it to do at the moment.
Here's a bad example:

  SELECT u.txt
  FROM smalltable t, (
    SELECT id, txt FROM largetable1
    UNION ALL
    SELECT id, txt FROM largetable2) u
  WHERE t.id = u.id
    AND t.foo = 'bar';

I was hoping that "smalltable" would get moved up into the union,
but it doesn't at the moment and the database does a LOT of extra
work.  In this case, I can manually do quite a couple of transforms
to move things around and it does the right thing:

  SELECT txt
  FROM (
    SELECT l.id as lid, r.id as rid, r.foo, l.txt
      FROM largetable1 l, smalltable r
    UNION ALL
    SELECT l.id as lid, r.id as rid, r.foo, l.txt
      FROM largetable1 l, smalltable r)
  WHERE foo = 'bar';
    AND lid = rid

The optimiser is intelligent enough to move the where clauses up
into the union and end end up with a reasonably optimal query.
Unfortunatly, in real life, the query is much larger and reorganising
everything manually isn't really feasible!

Is this a good place to ask about this or is it more in the realm
of the hackers mailing list?

Thanks,
  Sam


В списке pgsql-performance по дате сообщения:

От: John Arbash Meinel
Дата:
Сообщение: Re: slow queries, possibly disk io
От: Dawid Kuroczko
Дата:
Сообщение: Re: slow queries, possibly disk io