Designing an index for a postgres query

Question

We have a query that retrieves some data from a master-detail simple schema. the WHERE clause looks like:

-- These are just random numbers
Where ticket.type_id in ( 2, 3, 4, 5, 7 ) and
      (
         ticket.color_id is null or
         ticket.color_id in ( 1, 2 , 8 )
      )

we already have indexes in the columns: ticket.type_id and ticket.color_id, anyway the QUERY EXPLAIN ANALYZE still show us that Postgresql is making a sequential scan to satisfy the query.

This query is really important and recurrent in the system, so we want to create an index specially for this case.

What index could solve this case?

If you just do Where ticket.type_id in ( 2, 3, 4, 5, 7 ) will it use the index? — Mike Christensen
– Mike Christensen, Commented Jun 5, 2012 at 21:43
It probably believes doing a sequential scan of the table is faster than re-scanning the index for each ID in the IN clause. Unless the table is very large and the specified type_ids are a small fraction of the data, it's probably right. — Mike Christensen
– Mike Christensen, Commented Jun 5, 2012 at 21:55
in this case, is a big table (millions of records), any extra ideas? — SDReyes
– SDReyes, Commented Jun 5, 2012 at 21:56
Can you paste in the EXPLAIN for Where ticket.type_id in ( 2, 3, 4, 5, 7 )? — Mike Christensen
– Mike Christensen, Commented Jun 5, 2012 at 22:03
Please provide the whole query. Is it only a simple WHERE? Does it have HAVING? ORDER BY? LIMIT? How many tables are joined? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 5, 2012 at 23:44

T2Small · Accepted Answer · 2012-06-05 23:13:46Z

3

First, check that an index will actually help you. Turn off sequence scans to force index usage before your query is called:

SET ENABLE_SEQSCAN TO OFF;

After your query runs:

SET ENABLE_SEQSCAN TO ON;

to re-enable sequence scans. If this shows no performance improvement Postgres is already choosing the correct execution plan (sequence scan). I'd run an explain analyze <query> for the entire query with sequence scan both on and off.

Have you run a vacuum analyze on the relevant tables? It's possible the planner doesn't have correct or current statistics for your query.

answered Jun 5, 2012 at 23:13

T2Small

312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Randy · Accepted Answer · 2012-06-05 21:40:56Z

1

not real sure - but i think the null is getting yuo..

maybe an oddball looking structure like this

Where ticket.type_id in ( 2, 3, 4, 5, 7 ) and
      (
         nvl(ticket.color_id,1) in ( 1, 2 , 8 )
      )

answered Jun 5, 2012 at 21:40

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

Frank Heikens Over a year ago

Nvl() is An Oracle function. Use coalesce(), but this can't use an index.

Collectives™ on Stack Overflow

Designing an index for a postgres query

What index could solve this case?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What index could solve this case?

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related