I'm learning algebraic geometry, and I'm having some difficulties in developing intuition for the fibred product of schemes. I can take schemes to be over a field (but not necessarily separably closed). So my possibly too broad question is:
What is the best (i.e. most "intuitive") way to think about fibred product of schemes?
As a sub-question:
What is the best way to think about the open sets in a fibred product of schemes?
Intuition is really something you build after seeing lots of examples (and counter-examples). In the category of schemes the fibre product is a very general construction. Depending on the particular context the best way to think of it changes.
It could mean for example, restricting a family of varieties $X \rightarrow S$ parametrized by a base scheme $S$, to a subfamily parametrized by a smaller set of parameters $U \hookrightarrow S$. This would be the fibre product $X\times_S U$.
Sometimes it's just a base change of scalars. Like say, if $X=Spec \mathbb{Z}[x,y]/(y^2-x^3-x)$, then enlarging the ring of scalars from $\mathbb{Z}$ to $\mathbb{C}$ corresponds to the fibre product $X\times_\mathbb{Spec \mathbb{Z}} Spec\ \mathbb{C}$.
Yet another time it might be best to think of it as some kind of direct product. Say for example if $X$ is as above and $Y=Spec \mathbb{Z}[x]$, then $X\times Y$ is perhaps best thought of as just the direct product of two affine schemes.
Other times it's a combination of all of the above.
One thing to note about fibre products in the category of schemes, is how it unifies the seemingly different above examples. One could for instance still think of base change from $\mathbb{Z}$ to $\mathbb{C}$ as some kind of restriction of a family of varieties to a smaller parameter space. Or of $X\times Y$ as a constant family parametrized by $Y$ of schemes isomorphic to $X$, or a constant family of schemes isomorphic to $Y$ parametrized by $X$.
This appears a little unintuitive at first, but part of the power of schemes is how it allows one to apply geometric intuition to situations where there is hardly any picture you can imagine in your head, but where the algebraic formality is the same as if there were. To best use this, one must bring different intuitions from algebra and geometry and let them interact.
Regarding open sets. If you mean how to think locally, the obvious answer is to think of affine open sets of $X\times Y$ of the form $Spec A \otimes B$ where $Spec A$ and $Spec B$ are affine opens in $X$ and $Y$. In other words locally it's just the tensor product.