Re: Partitioning for query performance

Поиск
Список
Период
Сортировка
От Danny Lo
Тема Re: Partitioning for query performance
Дата
Msg-id 4bc42eff.1508c00a.1de5.6034@mx.google.com
обсуждение исходный текст
Список pgsql-novice

Hi,

 

I have a question regarding improving query performing using table partitioning:

 

I am a postgreSQL novice and have a table with  1-1.5TB of data. I am thinking about a range partition of the data using date ranges as my queries often condition on a date or range of dates in the WHERE clause. However, I often run my queries dynamically in a pl/pgsql function using the EXECUTE command. It goes something like this:

 

                              WHILE (current_date <= end_date) LOOP

                                            

                                             /* some code */

 

                                             EXECUTE ‘SELECT * from tablename

                                             where date = ‘’’ || current_date || ‘’’’;

                                            

                                             /* some more code */

                                             current_date := current_date + 1;

                              END LOOP;

 

 

In light of the below caveat from the postgreSQL manual, am I right to say there is no performance benefit from table partitioning?

Thanks very much!

The following caveats apply to constraint exclusion:

·        Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, "stable" functions such as CURRENT_DATE must be avoided.

 

В списке pgsql-novice по дате отправления:

Предыдущее
От: Syan Tan
Дата:
Сообщение: Re: Postgres 8.4 view keys
Следующее
От: "Machiel Richards"
Дата:
Сообщение: Autvacuum and Analyze stats?