I think the sub-select is in there because you prefetch 'rma'. You are not using it in the 'where' clause, so it is pretty much superfluous to the returned value; you are going to get a count of all 'shipment' records whether or not they have any associated 'rma' records.

If that's what you want, just re-write the query as :

my $count = Core::Models->resultset('Shipment')->search(
  undef
)->count();
[download]

Which gives the obvious

SELECT COUNT( * ) FROM shipment me

If you need a count of all shipments which have at least one 'rma' record, you need to re-write your query as:

my $count = Core::Models->resultset('Shipment')->search(
  { 'rma.id' => {'!=' => undef} },
  { prefetch => 'rma' }
)->count();
[download]

Which gives SQL of:

SELECT COUNT( * ) FROM (SELECT me.id FROM shipment me LEFT JOIN rma rm
+a ON ( rma.shipment_reference =  me.ref AND rma.merchant_id = me.api_
+merchant_id ) WHERE ( rma.id IS NOT NULL ) GROUP BY me.id) me
[download]

I think DBIC puts the sub-select in your case because it expects the 'rma' prefetch to be used in the 'where' clause, and thinks it will be using a LEFT JOIN (which isn't unreasonable).