From: Richard Huxton <dev@archonet.com>
To: Michael Artz <mlartz@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subselect query enhancement
Date: Thu, 01 Feb 2007 19:02:40 +0000
Message-ID: <45C23950.4070803@archonet.com> (raw)
In-Reply-To: <e9c163070702011027w11bbf2aud27096ad82fc6ba7@mail.gmail.com>
References: <e9c163070702010842ja639378v75e8c0eb757d9b72@mail.gmail.com>
<45C22223.1090909@archonet.com>
<e9c163070702011027w11bbf2aud27096ad82fc6ba7@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, mlartz@gmail.com
Subject: Re: Subselect query enhancement
In-Reply-To: <45C23950.4070803@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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