Problem with an Identical Query

From: Rob Schall <rschall(at)callone(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with an Identical Query
Date: 2007-03-01 14:40:40
Message-ID: 45E6E5E8.7040602@callone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

The queries:
First----

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)
-> Seq Scan on anitmp (cost=0.00..33.62 rows=945 width=8)
Filter: (istf = true)
-> Index Scan using i_destnum on current (cost=0.00..2137.36
rows=531 width=108)
Index Cond: (current.destnum = "outer".ani)
(5 rows)

Second----
calldetail=> EXPLAIN SELECT current.* FROM current JOIN anitmp ON
current.orignum=anitmp.ani AND istf=false;
QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=35.99..3402035.53 rows=5381529 width=108)
Hash Cond: ("outer".orignum = "inner".ani)
-> Seq Scan on current (cost=0.00..907191.05 rows=10170805 width=108)
-> Hash (cost=33.62..33.62 rows=945 width=8)
-> Seq Scan on anitmp (cost=0.00..33.62 rows=945 width=8)
Filter: (istf = false)
(6 rows)

The tables:
Table "public.current"
Column | Type | Modifiers
----------+-----------------------------+-----------
datetime | timestamp without time zone |
orignum | bigint |
destnum | bigint |
billto | bigint |
cost | numeric(6,4) |
duration | numeric(8,1) |
origcity | character(12) |
destcity | character(12) |
file | character varying(30) |
linenum | integer |
carrier | character(1) |
Indexes:
"i_destnum" btree (destnum)
"i_orignum" btree (orignum)

Table "public.anitmp"
Column | Type | Modifiers
--------+---------+-----------
ani | bigint |
istf | boolean |

Anyone have any ideas for me? I have indexes on each of the necessary
columns.

Rob

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2007-03-01 15:36:01 Re: Problem with an Identical Query
Previous Message Ilya Storozhilov 2007-03-01 14:34:32 BUG #3093: Error with converting error messages between server and client encodings