Обсуждение: phpPGAdmin Indexes, what does this do?

Hi all,

 

I have two databases set up, one for development and one for
production.  They are almost identical.  I noticed that a query was
taking about 6.7 seconds to execute on the development database, but
only .08 seconds on the production database.  The only difference was
there was another key for the production server (I am guessing it was an
index).  So I clicked on the "Index" link in PhpPGAdmin for the id of
the table on the development database.  This created another key just
like the one I had on the production.  Now the query executed at .08
seconds.  To further see what was going on, I removed the new index from
the dev DB and testing the query again... amazingly, too me, it was
still fast. Can anyone explain this to me?  Thanks, Sean. Below is my
query:

 

SELECT
   seasonal.id AS seasonal_id,
   seasonal.title AS seasonal_title,
   prod.id,
   prod.title,
   prod.co_title,
   prod.summary,
   prod.pic1,
   prod.new_pic,
   prod.new_date
  FROM
   seasonal,
   xref_seasonal_prod,
   prod,
   prod_opt
  WHERE
   seasonal.home = 't'
   AND xref_seasonal_prod.xref_seasonal_id = seasonal.id
   AND xref_seasonal_prod.xref_prod_id = prod.id
   AND prod.live = 't'
   AND (prod_opt.clearan = 'f' OR (COALESCE(prod_opt.quantity,0) -
COALESCE(prod_opt.committed,0)) > 0)
   AND prod_opt.xref_prod_id = prod.id
   AND prod_opt.live = 't'
   AND EXISTS
    (
     SELECT
      a.id
     FROM
      prod_opt AS a
     WHERE
      a.xref_prod_id = prod.id
      AND a.live = 't'
     ORDER BY
      a.priority
     LIMIT 1
    )
    AND prod.mrf_only = 'f'
  ORDER BY
   xref_seasonal_prod.priority,
   prod.title
  LIMIT 3