Pivotal Knowledge Base

Follow

Queries with limit clause on GPHDFS external tables are slower than expected

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Pivotal HD / Hadoop  

Symptom

When querying an external table that uses GPHDFS to read the data and using the limit clause the query can take an excessive amount of time to complete - generally a lot longer than selecting the full table.

For example:

padmin=# select count(*) from otp201001_large;
  count
---------
 6783517
(1 row)

Time: 29474.658 ms


gpadmin=# select * from otp201001_large limit 1; year | quarter | month | dayofmonth | dayofweek | flightdate | uniquecarrier | airlineid | carrier | tailnum | flightnum | origin | origincityname | originstate | originstatefips | originstatename | originwac | dest | destcityname | deststate | deststatefips | deststatename | destwac | crsdeptime | deptime | depdelay | depdelayminutes | depdel15 | departuredelaygroups | deptimeblk | taxiout | wheelsoff | wheelson | taxiin | crsarrtime | arrtime | arrdelay | arrdelayminutes | arrdel15 | arrivaldelaygroups | arrtimeblk ------+---------+-------+------------+-----------+------------+---------------+-----------+---------+---------+-----------+--------+----------------+-------------+-----------------+-----------------+-----------+------+----------------+-----------+---------------+---------------+---------+------------+---------+----------+-----------------+----------+----------------------+------------+---------+-----------+----------+--------+------------+---------+----------+-----------------+----------+--------------------+------------ 2010 | 1 | 1 | 3 | 7 | 03/01/10 | WN | 19393 | WN | N232WN | 923 | BWI | Baltimore, MD | MD | 24 | Maryland | 35 | SDF | Louisville, KY | KY | 21 | Kentucky | 52 | 2000 | 2032 | 32 | 32 | 1 | 2 | 2000-2059 | 7 | 2039 | 2200 | 3 | 2150 | 2203 | 13 | 13 | 0 | 0 | 2100-2159 (1 row) Time: 142140.034 ms gpadmin=#

Cause 

This is because of software defect MPP-26322 which is under investigation by Greenplum Engineering. 

Resolution

At this time, if a limit needs to be applied to a query the workaround is to copy the data to the Greenplum to increase performance. 

Comments

  • Avatar
    Gurupreet Singh Bhatia

    I tried this with select all rows in sub query and in upper query, i select the resultset from sub query with limit n. It worked as expected and take less time then above example, but still improvement required as time taken in GP (select with limit) is very less

Powered by Zendesk