Re: Improvement for query planner? (no, not about count(*) again ;-))

Tobias, 1st some etiquette stuff.

- You have replied just to me, directly. I'm CCing the list. Remember
to use reply all. Usual practice in the postgres lists is to reply to
the list and everyone involved in the thread ( doing reply all
achieves this normally ).

- It's not a biggie in this particular mail, but please do not
top-post, specially if you want to get answers on any complex
question. Trim unnecessary parts from the quoted text and reply below.
Having to scroll to a big chunk which includes even my signature is
not a thing I like.

On Mon, Jul 20, 2020 at 2:23 PM <tobias(dot)voelk(at)t-online(dot)de> wrote:
> I have tried the queries
> select name1 from games union select name2 from games
> but not
> select distinct name1 from games union
> select distinct name2 from games
> since it's just the same and easy for the optimizer to realize (I thought?)

There are several other things you have not done. You have not
provided any info ( statistics ) on your table, just the cardinality.
You have not provided any explain output, which is normally needed if
you really want people to help you.

On the subject, I'm not really sure both queries are identical or can
be optimized, but when one does bulk queries like that it is better to
help it. Your query is a corner case, a one of loading, and many
optimizers do not catch that things properly, as putting code for them
means increasing bug surface on a feature of dubious utility ( I,
personally, would prefer having to put your sample query manually than
risking optimizer bugs OR paying the price of the optimizer trying to
catch that on every query I send ).

Also, the optimizer may be catching many things, a explain output may
help to see what it's doing.

> I've given Postgres a few Gigs (I think 4?) as work_mem, having 16 GB in total. Still it's not using them.

This does not seem correct. work_mem is per operation, it can be used
several times on a single query. See
https://www.postgresql.org/docs/12/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY
. Again, some explain/show combos may clear up things.

> Seems like my mistake was creating that table with a primary key. But the query itself without inserting into anything should've been fast then, which it wasn't. I'll remember your trick of creating the primary key afterwards and will try just select name1 from games to see how it goes.

The PK stuff is bulk-loading 101. Try explain AND explain analyze of
some variants, remember to analyze your tables ( seems redundant, but
the PK & redundant hash key stuff leads me to think you are not too
experienced on postgres usage ).

Francisco Olarte.