Re: Subselect query enhancement

Michael Artz wrote:
>> > I have primary table that holds ip information
>> > and two other tables that hold event data for the specific IP in with
>> > a one-to-many mapping between them, ie:
>> [snip]
>> > There is quite a bit of commonality between the network_events and
>> > host_events schemas, but they do not currently share an ancestor.
>> > ip_info has about 13 million rows, the network_events table has about
>> > 30 million rows, and the host_events table has about 7 million rows.
>> > There are indexes on all the rows.
>>
>> What indexes though. Do you have (name,ip) on the two event tables?
>
> All the columns are indexed individually. The tables are completely
> static, as I reload the whole DB with new data every day.

The point of a (name,ip) index would be to let you read off ip numbers
in order easily.

>> How selective is "name" - are there many different values or just a few?
>> If lots, it might be worth increasing the statistics gathered on that
>> column (ALTER COLUMN ... SET STATISTICS).
>> http://www.postgresql.org/docs/8.2/static/sql-altertable.html
>
> I guess that is the heart of my question. "name" is not very
> selective (there are only 20 or so choices) however other columns are
> fairly selective for certain cases, such as 'port'. When querying on
> and unusual port, the query is very fast, and the single UNIONed
> subselect returns quickly. When 'port' is not very selective (like
> port = '80', which is roughly 1/2 of the rows in the DB), the dual
> subselect query wins, hands-down.
>
> And I have altered the statistics via the config file:
> default_statistics_target = 100
> Perhaps this should be even higher for certain columns?

You're probably better off leaving it at 10 and upping it for the vital
columns. 25 for names should be a good choice.

You could try partial indexes for those cases where you have
particularly common values of name/port:

CREATE INDEX idx1 ON host_events (ip) WHERE port=80;

--
Richard Huxton
Archonet Ltd