Re: WHERE condition not being pushed down to union parts

От: Tom Lane
Тема: Re: WHERE condition not being pushed down to union parts
Дата: ,
Msg-id: 10990.1240343911@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: WHERE condition not being pushed down to union parts  ("John L. Clark")
Ответы: Re: WHERE condition not being pushed down to union parts  ("John L. Clark")
Список: pgsql-performance

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

WHERE condition not being pushed down to union parts  ("John L. Clark", )
 Re: WHERE condition not being pushed down to union parts  (Tom Lane, )
  Re: WHERE condition not being pushed down to union parts  ("John L. Clark", )
   Re: WHERE condition not being pushed down to union parts  (Tom Lane, )
 Re: WHERE condition not being pushed down to union parts  ("John L. Clark", )
  Re: WHERE condition not being pushed down to union parts  (Tom Lane, )
   Re: WHERE condition not being pushed down to union parts  ("John L. Clark", )
    Re: WHERE condition not being pushed down to union parts  (Tom Lane, )

"John L. Clark" <> writes:
> I posted this earlier, but I haven't seen it come through the mailing
> list, perhaps because of the attachment.  I have also posted the
> attachment at <http://infinitesque.net/temp/union_performance_2009-04-21.postgresql.dump.gz>.

Ah.  The problem is that your view contains constants in the UNION arms:

CREATE VIEW uri_or_literal_object AS
    SELECT literalproperties.subject, literalproperties.subject_term, literalproperties.predicate,
literalproperties.predicate_term,literalproperties.object, 'L'::character(1) AS object_term, literalproperties.context,
literalproperties.context_term,literalproperties.data_type, literalproperties.language FROM literalproperties 
UNION ALL
    SELECT relations.subject, relations.subject_term, relations.predicate, relations.predicate_term, relations.object,
relations.object_term,relations.context, relations.context_term, NULL::bigint AS data_type, NULL::character varying(3)
ASlanguage FROM relations; 

In 8.2 and 8.3, the planner is only smart enough to generate
inner-indexscan nestloop plans on UNIONs if all the elements of the
SELECT lists are simple variables (that is, table columns).
8.4 will be smarter about this.

            regards, tom lane


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

От: david@lang.hm
Дата:
Сообщение: Re: performance for high-volume log insertion
От: Robert Haas
Дата:
Сообщение: Re: performance for high-volume log insertion