Paginate is 4-5X slower than returning all records

[drewgallagher]

I have a query that is taking 50+ seconds right now locally.

When I run it with paginate it's 50+ seconds, but when I drop the paginate it returns all of the records in 15 seconds.

Because we have a ton of data, this is okay to return in ~ 15-20 seconds.

Why is the paginate adding so much time and how can we fix this?

Here is a video I sent to a colleague demonstrating the issue

https://www.loom.com/share/ffc1375e08e2445580593820ddbc8ef6?sid=2627d20b-8ac9-4508-865d-97b698b8f7f0

Even trying simplePaginate is slow

Here is the query

query {
	playbook_campaigns(
		page: 1
		limit: 10
		sort: "-ltv"
		exclude_without_ltv: 0
		general_metric_filters: [
			{ metric: "score", operator: ">", value1: 0, value2: null }
		]
		client_ids: [3240, 1024]
		start_date: "2021-08-01"
		end_date: "2024-08-31"
		match_name_in_all_models: 1
		platform_ids: [1001, 1002]
	) {
		data {
			id
			name
			client {
				id
				name
				logo
				industry {
					id
					name
				}
				business_type {
					id
					name
				}
				playbook_platform_match_rates {
					platform {
						id
						name
						slug
					}
					match_rate
				}
			}
			first_asset {
				domain_url
				url
				thumbnail
			}
			objective
			status
			spend
			roas
			revenue
			stop_rate
			retention_rate
			hold_rate
			score
			ctr
			cpc
			cpm
			cpa
			cvr
			clicks
			impressions
			conversions
			slug
			updated_at
			created_at
			last_refreshed
			ecomm_matched_metric {
				id
				ltv
				cac
			}
		}
		current_page
		from
		has_more_pages
		last_page
		per_page
		to
		total
	}
}

[mfn]

Please measure the result time without returning anything in GraphQL, just benchmark the get vs. the paginate calls in the resolver: does it show the same behaviour?

[drewgallagher]

I ran the following code to get the output of paginate vs get(). I had to reduce the date range of the query because it was timing out with 2 years of data. But with 1 year of data it's more than double the time for paginate, which scales at higher amounts of data.

$start = microtime(true);
$playbook_campaign->paginate($args['limit'], ['*'], 'page', $args['page']);
$end = microtime(true);
dump('Execution time using pagination: ' . ($end - $start) . ' seconds');

$start = microtime(true);
$playbook_campaign->get();
$end = microtime(true);
dd('Execution time using get() only: ' . ($end - $start) . ' seconds');

[drewgallagher]

@mfn Using xdebug, I stepped into the paginate function and timed it, and it's taking 36 seconds to get the $total

[mfn]

But AFAIK this graphql library is not involved in those code parts, right? 🤔

[drewgallagher]

@mfn I believe you are correct because paginate comes from laravel? So this seems like a laravel/sql issue.

I think at first I thought the paginate was a part of this library, but it looks like it's just calling the laravel framework paginate.

[mfn]

Exactly, that's what I'm thinking right now.

Would be interesting to see both raw SQL statements, nicely formatted; but there's also more to it (data volume, cardinality, indices) and in the end it seems unrelated to this library.

This library provides the built-in SelectFields utility class which is concerned with SQL interaction, but AFAICS you are not using it (and: I'm also not advising to use it).