Thread: is there a relationship between indexes and temporary file creation?

Seref Arikan <serefarikan@kurumsalteknoloji.com> writes:
> I have a table with 10s of millions of rows and I'm running a fairly
> complex query with a lot of self joins.

> This is an experimental db and the table structure needs to stay as it is.
> I've noticed that some queries are not completed because postgres uses all
> available space on disk for temporary data when doing complex joins.

> The table has no indexes at the moment; I'm measuring performance with and
> without indexes so I don't mind long query times. Running out of disk space
> is a problem though.I can't think of a connection between indexes and the
> temporary space needed for joins but I hope I can get some input from those
> with (a lot) more knowledge about potgres internals.

> Is there any possibility of indexing decreasing the required temporary disk
> space when performing complex joins?

Hm.  Your alternatives don't seem terribly good here.  Presumably you're
running out of space because either a merge or hash join will spill temp
data to disk if there's too much data.  While an indexed nestloop join
wouldn't have that problem, for the amount of data you're apparently
dealing with, you might be dead of old age before it finishes :-(.
A slightly more promising idea is that an indexscan might substitute for
the sort needed by a mergejoin, so again not needing temp space; but
this still likely implies a whole lot of random I/O which will take
forever, unless maybe your data is more or less in order by the join key.

In short I doubt that indexes will be magic bullets for enormous joins.
You probably need to be smarter about what the queries are asking for.
But without a lot more detail than you've provided here, we're not going
to be able to help much about that.

If you'd like additional advice, I'd recommend reviewing
https://wiki.postgresql.org/wiki/Slow_Query_Questions
and then taking the discussion to the pgsql-performance list.

            regards, tom lane

Seref Arikan <serefarikan@kurumsalteknoloji.com> writes:
> Another idea that popped into my head is to consider table partitioning. Am
> I correct to assume that (inevitable) joins could benefit from partitions
> when sub query criteria constraints results to a particular partition?

That's too vague to answer really.  My suspicion is that you'd still end
up having to modify the queries, ie hand-factoring the joins, but maybe
you wouldn't need to.  You could experiment easily enough; just set up a
toy database with not much data in it and see if you get plans that don't
scan all the partitions.

            regards, tom lane