Re: JOINS and non use of indexes - Mailing list pgsql-sql

From Ian Cass
Subject Re: JOINS and non use of indexes
Date
Msg-id 00e001c1df10$5c34a7b0$6602a8c0@salamander
Whole thread Raw
In response to JOINS and non use of indexes  ("Ian Cass" <ian.cass@mblox.com>)
List pgsql-sql
> There's your problem (and don't tell me Oracle gets this right; they
> don't do inheritance, do they?).  The planner isn't smart about
> indexscan-based joins for inheritance trees.  This could possibly be
> improved with some work, but I haven't thought about the details.

It's true. When I use the monthly tables explicitly and not it's parent, it
uses the index properly. Unfortunately this isn't the solution I was looking
for :/

select * from messages_200203 as messages, statusinds_200203 as statusinds
WHERE (messages.client_id = '7' AND messages.user_name in ('U66515'))
AND statusinds.gateway_id = messages.gateway_id
limit 5;

Limit (cost=0.00..20.69 rows=5 width=455)
-> Nested Loop (cost=0.00..13495.79 rows=3261 width=455)
-> Index Scan using messages_200203_ix2 on messages_200203 messages
(cost=0.00..272.61 rows=68 width=383)
-> Index Scan using statusinds_200203_ix1 on statusinds_200203 statusinds
(cost=0.00..194.87 rows=48 width=72)

--
Ian Cass



pgsql-sql by date:

Previous
From: "Ian Cass"
Date:
Subject: Re: JOINS and non use of indexes
Next
From: Tom Lane
Date:
Subject: Re: JOINS and non use of indexes