Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table instead. The same thing also apply when using a Subquery instead of a View.

For example:

Select something from (select unnest(an_array_field) from table_y) 
where date = 'somedate' and state = 'somestate'

We currently have 2 different servers running 2 different version of Greenplum. Server A run on an older version (4.2.1) while the Server B run on 4.2.3. Running the same query above will result differently. Server A (old) will return the query in few seconds while Server B (new) will take forever to return. Running an Explain of the query shows that Server A only do scan on one of the partitions (with the date and state in the where clause) while Server B will do a Scan on each partition, causing the slowness.

The table structure for both DBs are the same. Running a query without the unnest will not have the problem. So, I'm suspecting that there is something to do with new version. Is there anything I can do to solve this problem?

share|improve this question
    
I didn't know that there is no "Greenplum" tag yet.. am I in the wrong place? –  Phelios Mar 12 '13 at 1:57
    
You're definitely in the right place, it's just not a common topic around here (I'd never heard of it before seeing this question). –  Jon Seigel Mar 12 '13 at 2:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.