Thread: explain ?

I had a query which was dog slow. Explain showed the following:

                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7878.67..7878.67 rows=1 width=24)
   ->  Nested Loop  (cost=0.00..7878.66 rows=1 width=24)
         ->  Seq Scan on trans  (cost=0.00..7875.63 rows=1 width=20)
               Filter: ((firsttimei = 2) AND (sourcepeeraddress = '192.168.0.1'::inet) AND (flowindex = 2))
         ->  Index Scan using stats_pkey on stats  (cost=0.00..3.02 rows=1 width=4)
               Index Cond: (stats.id = "outer".stats_id)
               Filter: (timeslice < 'Thu 13 Mar 00:00:00 2003'::timestamp without time zone)
(7 rows)


I created an index on firsttimei, vacuum full analysed, and explain showed me
*exactly* the same thing(!) The difference being that the query is now
lightning fast :-) So, what is explain actually telling me? The diffence
is the Filter: returns true|false more quickly because of the index?

Cheers,

Patrick

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> I created an index on firsttimei, vacuum full analysed, and explain showed me
> *exactly* the same thing(!) The difference being that the query is now
> lightning fast :-)

Perhaps the vacuum got rid of a whole bunch of dead rows?

            regards, tom lane

On Fri, Apr 18, 2003 at 10:20:33AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > I created an index on firsttimei, vacuum full analysed, and explain showed me
> > *exactly* the same thing(!) The difference being that the query is now
> > lightning fast :-)
>
> Perhaps the vacuum got rid of a whole bunch of dead rows?

I had done a vacuum full earlier.. and by dog slow versus lightning
fast, I mean 1 unit of data inserted in just over an hour versus
8.6 units of data inserted in 2 mins 29 seconds(!) Well, I'm
certainly not complaining!

Cheers,

Patrick