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?

  • I didn't know that there is no "Greenplum" tag yet.. am I in the wrong place? – Sufendy 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.