Re: Intersect/Union X AND/OR

On Mon, Dec 5, 2011 at 14:14, Thiago Godoi <thiagogodoi10(at)gmail(dot)com> wrote:
> My original query :
>
> select table1.id
> from table1, (select function(12345) id) table2
> where table1.kind = 1234
> and table1.id = table2.id
>
> "Nested Loop  (cost=0.00..6.68 rows=1 width=12)"
> "  Join Filter: ()"
> "  ->  Seq Scan on recorte  (cost=0.00..6.39 rows=1 width=159)"
> "        Filter: (id = 616)"
> "  ->  Result  (cost=0.00..0.26 rows=1 width=0)"

Note that this EXPLAIN output is quite different from your query.
Intead of a "kind=1234" clause there's "id=616". Also, please post
EXPLAIN ANALYZE results instead whenever possible.

> When I changed the query to use intersect :
[...]
> The second plan is about 10 times faster than the first one.

Judging by these plans, the 1st one should not be slower.

Note that just running the query once and comparing times is often
misleading, especially for short queries, since noise often dominates
the query time -- depending on how busy the server was at the moment,
what kind of data was cached, CPU power management/frequency scaling,
etc. ESPECIALLY don't compare pgAdmin timings since those also include
network variance, the time taken to render results on your screen and
who knows what else.

A simple way to benchmark is with pgbench. Just write the query to a
text file (it needs to be a single line and not more than ~4000
characters).
Then run 'pgbench -n -f pgbench_script -T 5' to run it for 5 seconds.
These results are still not entirely reliable, but much better than
pgAdmin timings.

Regards,
Marti