I much prefer pies that are baked with a very tart apple, which mellows out and gives lots of complex flavour when cooked. Back home we had "cooking apples" especially meant for this which were basically inedible when raw. Now I'm in the US and can't find such apples and can't stand the apple pies here that are overly sweet and bland.
I usually use half Grannie Smith and half something more aromatic in a pie. The Grannie provides firmness and tartness and the other varieties provide more appleiness. I control the sweetness with how much sugar I add, usually using less than the recipes call for.
Have you tried going to a farmers market? Ask the sellers what apple to use for pie, and they'll direct you to cooking apples. This is especially true if you go to an Amish market.
hard to find commercially in the US but we tend to call them "crabapples" (many crabapples are too small or only suitable for juicing but some are large enough for culinary use), or you can look for some cider homebrewing enthusiasts who may know how to source tart/dry apples in your area.
Red delicious is a terrible pie apple, unless you hate the flavor of apples and only want sugary cinnamonny mush.
My favourite apple variety right now is the organic Cripp, aka Pink Lady. Lovely aroma and flavours. I used to eat Granny Smiths in my youth, but they're too tart and green compared to those nice organic Cripps.