Very Slow Query (but fast on mysql)

[samuraraujo]

I have a very slow query on when using mysql_fdw.

The query is not slow when executed directly on mysql.

Table definition:

CREATE TABLE `cluster_modelleddata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cluster_id` int(10) unsigned NOT NULL,
  `year` smallint(4) NOT NULL,
  `category` varchar(64) NOT NULL,
  `key` varchar(64) NOT NULL,
  `value` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cluster_year_category_key` (`cluster_id`,`year`,`category`,`key`),
  KEY `idx_cluster_modelleddata_value` (`value`),
  CONSTRAINT `cluster_modelleddata_ibfk_1` FOREIGN KEY (`cluster_id`) REFERENCES `clusters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=165852942 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

Query:

SELECT
            value
        FROM
            cluster_modelleddata
        WHERE
              cluster_modelleddata.category = 'carbon_model'
        AND cluster_modelleddata."key" = 'carbon_intensity_actual'
        ORDER BY "year" DESC
        LIMIT 1

Any clue what it can be causing the issue?

[munro]

Run show full processlist; immediately on the MySQL server after invoking in PG, cheeky way to see what it's doing. :)

But if you check out the features [1], mysql_fdw doesn't say it supports join, order by, or limit push down, so that's probably the issue. If you have free time, it would be awesome if you could add those features!

Work around is just pull data into temp tables (or materialized views) and work off that.

create temp table cluster_modelleddata_temp as select * from cluster_modelleddata;

[1] https://github.com/EnterpriseDB/mysql_fdw#enhancements

[ahsanhadi]

Hi Munro,

The join and sort push down feature is already added to PG FDW Infrastructure, we need to add support for join and sort push down to mysql_fdw. I will get this added to our product management feature list for mysql_fdw. Hopefully we will have time to get it done soon.

-- Ahsan

[munro]

@ahsanhadi That's awesome! I did notice postgres_fdw was a bit snappier 😄

[oberien]

Are there any improvements on pushing down joins? I'm really looking forward to having it implemented.

Actually I'd like to have some sort of setting, allowing or preventing push downs of joins. In some cases, executing the joins in postgres is actually faster than doing the same in mysql.

[ahsanhadi]

Due to other pressing items, we haven't been able to work on adding the push-down enhancements in PG 9.6 to mysql_fdw. It is still on our roadmap to be done soon.

[jmealo]

@ahsanhadi Any updates on this? We're quickly approaching the PG 10 release and still catching up on 9.6.

[jackrabbithanna]

How are the pushdowns coming along?

[Magmatrix]

Hi,

Can you give us a short progress report on the pushdowns? Is it being worked on or has it been postponed?

The reason I'm asking is that without ORDER BY pushdown and LIMIT pushdown, large tables are really hard to use. A SELECT with LIMIT 1 from a 100GB+ table takes half an hour instead of a few milliseconds ... :(