Re: Differences in identical queries

Rob Schall wrote:
> Question for anyone...
>
> I have to queries. One runs in about 2 seconds. The other takes upwards
> of 2 minutes. I have a temp table that is created with 2 columns. This
> table is joined with the larger database of call detail records.
> However, these 2 queries are handled very differently.

1. They're different queries - the second is expecting 10 times as many
rows as the first.
2. Can't tell if that is accurate - you need to supply EXPLAIN ANALYSE
output instead of EXPLAIN, so we can see what actually happened.

> calldetail=> EXPLAIN SELECT current.* FROM current JOIN anitmp ON
> current.destnum=anitmp.ani AND istf=true;
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..2026113.09 rows=500908 width=108)

> calldetail=> EXPLAIN SELECT current.* FROM current JOIN anitmp ON
> current.orignum=anitmp.ani AND istf=false;
> ---------------------------------------------------------------------------
>  Hash Join  (cost=35.99..3402035.53 rows=5381529 width=108)


--
   Richard Huxton
   Archonet Ltd