От: Tom Lane
Тема: Re: query optimization
Дата: ,
Msg-id: 12228.1335467838@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: query optimization  (Richard Kojedzinszky)
Ответы: Re: query optimization  (Thomas Kellerer)
Re: query optimization  (Richard Kojedzinszky)
Список: pgsql-performance

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

query optimization  (Richard Kojedzinszky, )
 Re: query optimization  ("Kevin Grittner", )
 Re: query optimization  (Tom Lane, )
  Re: query optimization  (Thomas Kellerer, )
   Re: query optimization  (Tom Lane, )
    Re: query optimization  (Andrew Dunstan, )
  Re: query optimization  (Richard Kojedzinszky, )

Richard Kojedzinszky <> writes:
> Dear list,
> We have a database schema, which looks the same as the attached script.

> When filling the tables with data, and skipping analyze on the table (so
> pg_stats contains no records for table 'a'), the first select in the
> script runs fast, but after an analyze the planner decides to sequence
> scan tables b and c, thus making the query much slower. Can somebody help
> me solving this issue, or tuning our installation to not to use sequence
> scans in this case?

Um ... did you analyze all the tables, or just some of them?  I get
sub-millisecond runtimes if all four tables have been analyzed, but it
does seem to pick lousy plans if, say, only a and b have been analyzed.

What you really need for this query structure is the parameterized-path
work I've been doing for 9.2; but at least on the exact example given,
I'm not seeing that 9.1 is that much worse.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Weird plan variation with recursive CTEs
От: AI Rumman
Дата:
Сообщение: NOT EXISTS or LEFT JOIN which one is better?