Re: Simple join doesn't use index

On Monday, January 28, 2013, Alex Vinnik wrote:

> It sure turned out that default settings are not a good fit. Setting random_page_cost
> to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
> being used in explain plan and IO utilization is close to 0.
>

This is not surprising. The default settings are aimed at quite small
servers, while you seem to have a rather substantial one. Have you done
anything yet to fix effective_cache_size?

> QUERY PLAN
> Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
> time=2176.045..2418.162 rows=241238 loops=1)
>

However I noticed that sorting is done using disk("external sort Disk:
> 4248kB")
>

As far as pgsql is concerned, it is using the disk. But the kernel is
probably buffering that to an extent that the disk is probably being
touched rather little. So I wouldn't worry about it.

> which prompted me to take a look at work_mem. But it turned out that
> small increase to 4MB from default 1MB turns off index usage and query gets
> x10 slower. IO utilization jumped to 100% from literally nothing. so back
> to square one...
>
> QUERY PLAN
> Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual
> time=33200.762..33474.443 rows=241238 loops=1)
>

And why should the IO utilization have jumped? Is everything in memory, or
is it not? You should run your EXPLAINs with (analyze, buffers), and also
you should turn on track_io_timings, at least in the local session; that
will give us some insights.

If everything is in memory, then why is the seq scan taking so long? If
not, then why is the nested loop such a good idea? (In my hands, when
everything does *not* fit in memory, the nested loop is very very bad)

You seem have a bit of an infatuation with Dec 15th, running that one query
over and over and over. Why? If the real
live query is not just for that one day repeatedly, then you should test
with different days, not just one day repeatedly. (And if your real query
really is like the movie "Groundhog Day", you should probably cluster or
partition with that in mind.)

Anyway, there was an issue introduced in 9.2.0 and to be removed in 9.2.3
which over-penalized nested loops that had large indexes on the inner side.
Since your different plans are so close to each other in estimated cost, I
think this issue would be enough to tip it into the seq scan. Also, your
poor setting of effective_cache_size might also be enough to tip it. And
both combined, almost certainly are.

But ultimately, I think you are optimizing for a case that does not
actually exist.

Cheers,

Jeff