Re: PostgreSQL OR performance

Віталій Тимчишин wrote:
> I am sorry, I've emptied atom_match table, so one part produce 0 result, but
> anyway here is explain:

David's right - the total estimate is horribly wrong

> "Merge Join (cost=518771.07..62884559.80 rows=1386158171 width=32) (actual
> time=30292.802..755751.242 rows=34749 loops=1)"

But it's this materialize that's taking the biggest piece of the time.

> " -> Materialize (cost=469981.13..498937.42 rows=2316503 width=30)
> (actual time=15915.639..391938.338 rows=242752539 loops=1)"

15.9 seconds to 391.9 seconds. That's half your time right there. The
fact that it's ending up with 242 million rows isn't promising - are you
sure the query is doing what you think it is?

> " -> Sort (cost=469981.13..475772.39 rows=2316503 width=30) (actual
> time=15915.599..19920.912 rows=2316503 loops=1)"
> " Sort Key: production.company.run_id"
> " Sort Method: external merge Disk: 104896kB"

By constrast, this on-disk sort of 104MB is comparatively fast.

> P.S. May be I've chosen wrong list and my Q better belongs to -hackers?

No - hackers is if you want to discuss the code of the database server
itself.

--
Richard Huxton
Archonet Ltd