pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
appropriate indexing
2+ messages / 2 participants
[nested] [flat]

* appropriate indexing
@ 2004-11-04 22:32  T E Schmitz <mailreg@numerixtechnology.de>
  0 siblings, 1 reply; 2+ messages in thread

From: T E Schmitz @ 2004-11-04 22:32 UTC (permalink / raw)
  To: pgsql-performance

Hello,
I am seeking some advice on appropriate indexing. I think I have a rough 
idea where to place my indices but would be grateful for some tips from 
more experienced people.
The following example shows what is probably the most complex query of 
the application.

A few points to give you a rough indicator about the DB:
- application is more query than update intensive
- each table has a surrogate PK (serial)
- access of tables ITEM and PRODUCT always involves join on BRAND, 
MODEL, TYPE
- CATEGORY,SECTION,CONDITION are pretty much static and have no more 
than 30 rows
- PRODUCT table will eventually contain a few thousand records
- ITEM table will, grow, grow, grow (sold items are not deleted)
- PRODUCT_FK, TYPE_FK, MODEL_FK, BRAND_FK are never NULL
- PRODUCT_LENS... columns are only NOT NULL where  CATEGORY_PK=2
- ITEM.STATUS = available, sold, reserved ..., never NULL
- ITEM.KIND = secondhand, commission, new, never NULL

=============================================
My understanding is:
- index the FK columns used for joins
- index columns typically used in WHERE clause
- index on e.g. PRODUCT.CATEGORY_FK prevents seq scan of CATEGORY
- as CATEGORY contains few rows it's not worth indexing CATEGORY_FK

Questions:
- Does the order of the JOIN clauses make a difference?
- Does the order of the WHERE clauses make a difference?

=============================================
SELECT

BRAND.BRAND_NAME,
MODEL.MODEL_NAME,
TYPE.TYPE_NAME,
ITEM.RETAIL_PRICE,
CONDITION.ABBREVIATION

FROM ITEM

LEFT JOIN PRODUCT ON ITEM.PRODUCT_FK=PRODUCT.PRODUCT_PK
LEFT JOIN TYPE ON PRODUCT.TYPE_FK=TYPE.TYPE_PK
LEFT JOIN MODEL ON TYPE.MODEL_FK=MODEL.MODEL_PK
LEFT JOIN BRAND ON MODEL.BRAND_FK=BRAND.BRAND_PK
LEFT JOIN CATEGORY ON PRODUCT.CATEGORY_FK=CATEGORY.CATEGORY_PK
LEFT JOIN SECTION SECTION ON PRODUCT.SECTION_USED_FK=SECTION.SECTION_PK
LEFT JOIN CONDITION ON ITEM.CONDITION_FK=CONDITION.CONDITION_PK

WHERE PRODUCT.SECTION_USED_FK IS NOT NULL AND ITEM.STATUS=1 and 
(ITEM.KIND=2 or ITEM.KIND=3)

ORDER BY SECTION.POSITION, CATEGORY.POSITION,
PRODUCT.LENS_FOCAL_LEN_FROM,PRODUCT.LENS_FOCAL_LEN_TO IS NOT NULL,
PRODUCT.LENS_FOCAL_LEN_TO,
PRODUCT.LENS_SPEED_FROM,PRODUCT.LENS_SPEED_TO,
TYPE.TYPE_NAME, CONDITION.POSITION


I'd appreciate a few pointers based on this example. Thanks in advance.

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: appropriate indexing
@ 2004-11-04 22:38  Matt Clark <matt@ymogen.net>
  parent: T E Schmitz <mailreg@numerixtechnology.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Matt Clark @ 2004-11-04 22:38 UTC (permalink / raw)
  To: mailreg@numerixtechnology.de; +Cc: pgsql-performance


> - ITEM table will, grow, grow, grow (sold items are not deleted)
> WHERE PRODUCT.SECTION_USED_FK IS NOT NULL AND ITEM.STATUS=1 and 
> (ITEM.KIND=2 or ITEM.KIND=3)
>
Partial index on item.status ?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-11-04 22:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-04 22:32 appropriate indexing T E Schmitz <mailreg@numerixtechnology.de>
2004-11-04 22:38 ` Matt Clark <matt@ymogen.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox