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.voelk@xxxxxxxxxxx> 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.
