Re: Query does not use index

Hi,

Am 30.04.2004 um 01:32 schrieb Tom Lane:

> Martin Hampl  writes:
>> It would be very nice, if someone could have a look at the query I'm
>> trying to optimize. At the moment, I don't understand PostgreSQL's
>> behaviour and are stuck. Thanks a lot in advance.
>
> Did you ANALYZE these tables?

I did.

> Also, please post EXPLAIN ANALYZE not
> just EXPLAIN when complaining about bad plans.  Since the essence of
> your complaint is that the planner's estimates are wrong, showing us
> only estimates and not reality makes it hard to give constructive
> suggestions ...

OK.

bnc23Mio=# EXPLAIN ANALYZE select * from
bnc23Mio-#     token,
bnc23Mio-#     s
bnc23Mio-# where
bnc23Mio-#     token.word = 'FACTSHEET' and
bnc23Mio-#     s.text_id = token.text_id and
bnc23Mio-#     s.start = token.position;
                                                              QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------
  Nested Loop  (cost=0.00..39120.95 rows=1 width=32) (actual  
time=102.263..692248.553 rows=3 loops=1)
    ->  Index Scan using s_pkey on s  (cost=0.00..24698.44 rows=3367  
width=16) (actual time=75.933..589743.642 rows=1111220 loops=1)
    ->  Index Scan using token_pkey on token  (cost=0.00..4.27 rows=1  
width=16) (actual time=0.086..0.086 rows=0 loops=1111220)
          Index Cond: (("outer".text_id = token.text_id) AND  
("outer"."start" = token."position"))
          Filter: ((word)::text = 'FACTSHEET'::text)
  Total runtime: 692249.314 ms


bnc23Mio=# EXPLAIN ANALYZE select * from (select text_id, position from  
token where word = 'FACTSHEET') t left join s on (s.text_id = t.text_id  
and  s.start = t.position );
                                                                 QUERY  
PLAN
------------------------------------------------------------------------ 
-------------------------------------------------------------------
  Hash Left Join  (cost=24715.28..56630.78 rows=7892 width=24) (actual  
time=255329.976..255355.967 rows=5 loops=1)
    Hash Cond: (("outer".text_id = "inner".text_id) AND  
("outer"."position" = "inner"."start"))
    ->  Index Scan using word_idx on token  (cost=0.00..31402.51  
rows=7892 width=8) (actual time=91.010..109.394 rows=5 loops=1)
          Index Cond: ((word)::text = 'FACTSHEET'::text)
    ->  Hash  (cost=24698.44..24698.44 rows=3367 width=16) (actual  
time=255236.914..255236.914 rows=0 loops=1)
          ->  Index Scan using s_pkey on s  (cost=0.00..24698.44  
rows=3367 width=16) (actual time=105.100..247798.661 rows=1111220  
loops=1)
  Total runtime: 255502.736 ms

Maybe that *is* what i wanted it to do? However, searching just for  
'FACTSHEET' is very quick (I rebooted before this query to clear any  
cache---is there a better way to do this?):

bnc23Mio=# EXPLAIN ANALYZE select * from token where word = 'FACTSHEET';
                                                          QUERY PLAN
------------------------------------------------------------------------ 
----------------------------------------------------
  Index Scan using word_idx on token  (cost=0.00..31402.51 rows=7892  
width=16) (actual time=102.350..125.032 rows=5 loops=1)
    Index Cond: ((word)::text = 'FACTSHEET'::text)
  Total runtime: 125.289 ms

and I would have thought that the results of this query could have been  
used to search for the respective records in s (using on of the  
indexes)?

Regards,
Martin.