I think there’s room for a little bit of nuance here

It’s pretty much always going to mean you’ve been a little dumb, or naive, etc. so it’s an insult in that sense.

But I tend to hear it used by southerners in sort of a “aw, they don’t know any better/can’t help it” sort of way.

So more like when I call my dog an idiot because she got her leash tangled around a street sign again. There’s no malice in it, she’s dumb, but she can’t help it, she’s just a dog.

Not like if I call my coworker an idiot because he’s continuing to fuck up the same basic shit no matter how or how many times you explain it to him, he should know better and can help it.

Now, there’s arguably another level of insult/disrespect implied because they are talking about you in the same way they would a dog or a small child. I don’t think that’s really something they usually intend, but of course the intention isn’t really as important as how the target actually feels about it. You can also probably get really psychological about it and dig into how their word choice reflects how they view others in relation to themselves or something, but I’m neither interested in nor qualified to go down that rabbit hole right now.

Disclaimer- those are just hypothetical examples I gave, both my dog and my coworkers are actually pretty pretty smart and capable. She’s actually the first dog I’ve ever had who actually seems to understand how to not get tangled on a street sign, so I mean no disrespect to her, or to my coworkers either I guess.