Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group