I have three tables in a PostgreSQL database that I'm querying via a view and some joins.

CREATE TABLE network_info (
  network         CIDR          NOT NULL,
  some_info       TEXT          NULL,
  PRIMARY KEY (network)

CREATE TABLE ipaddr_info (
  ipaddr          INET          NOT NULL,
  some_info       INT           NULL,
  PRIMARY KEY (ipaddr, some_info)

CREATE TABLE ipaddrs (
  addr            INET          NOT NULL,
  PRIMARY KEY (addr)

CREATE VIEW ipaddr_summary AS
  i.addr                  AS ip_address,
  a.some_info             AS network_info,
  COUNT(b.ipaddr)         AS ip_info_count
FROM ipaddrs AS i
LEFT JOIN network_info AS a
  ON (i.addr << a.network)
LEFT JOIN ipaddr_info AS b
  ON (i.addr = b.ipaddr)
GROUP BY i.addr, a.some_info

All of the tables have ~150K rows right now, and it takes a really long time (~3 hours) to run SELECT * from ipaddr_summary; on an Intel Pentium 4 2.8GHz dual core with 2G of memory running PostgreSQL 9.3.

Is there a way I can restructure or optimize this particular schema or view to make the query faster, or is it a hardware issue? I'm going to spin up a beefy VM in the cloud and test, but wanted to see if there's a way to optimize w/out just throwing more hardware at it.

  • Your Postgres version is a must for performance questions. – Erwin Brandstetter Jan 26 '15 at 23:53
  • Edited OP to include version - PostgreSQL 9.3. – Bryan Jan 27 '15 at 0:19
  • Table definition of ipaddrs is incomplete. PK? Obviously you edited out parts manually ... – Erwin Brandstetter Jan 27 '15 at 1:47
  • I did edit out parts manually. Fixed missing PK. – Bryan Jan 27 '15 at 1:52

There might be hardware issues, too - how should we know? But there are certainly issues with the query.

First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation:

Arriving at this (cleaned up) query:

SELECT i.addr      AS ip_address
     , a.some_info AS network_info
     , COUNT(b.ipaddr) AS ip_info_count
FROM   ipaddrs           i
LEFT   JOIN ipaddr_info  b  ON i.addr = b.ipaddr
LEFT   JOIN network_info a  ON i.addr << a.network
GROUP  BY 1,2;

Next, the query looks suspiciously like it's going very wrong. Two uncorrelated joins can multiply rows:

With 150k rows in each table, there is potential for a huge (unintended) Cartesian product. My educated guess, you really want this:

SELECT addr        AS ip_address
     , a.some_info AS network_info
     , b.ip_info_count
FROM   ipaddrs i
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
LEFT   JOIN network_info a ON i.addr << a.network

I suspect that GROUP BY is also not needed in the outer SELECT now. Besides fixing the count, this might also be faster by several orders of magnitude, avoiding the proxy cross-join.

You could first join to ipaddrs (especially if you have predicates filtering rows from it) and then aggregate, or first aggregate in the subquery like displayed. Usefulness of this variant largely depends on data distribution. It's great for few ipaddr with big counts. Details:

Finally, you need index support. Equality between ipaddr and addr is covered by the default btree indexes of the PRIMARY KEY. The query on the whole table is probably using a sequential scan anyway.

For the "is contained by" operator << operator you'll need a GIN or GiST index. The best option would be the new inet_ops GiST index operator class in Postgres 9.4 (supports data types inet and cidr):

CREATE INDEX ON network_info USING gist (network inet_ops);

Not sure if the index can be used in a plain INNER (or OUTER) join. Can't test right now. Maybe you need correlated subqueries or a LATERAL join to utilize the index:

SELECT addr AS ip_address
     , a.network_info
     , b.ip_info_count
FROM   ipaddrs i
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
   SELECT some_info AS network_info
   FROM   network_info
   WHERE  network >> i.addr
   ) a ON TRUE;

Advice for indexing in older versions:

  • Thank you for taking the time to answer my question. I'll read through all the posts and documentation to understand what you're suggesting and try out your suggestions ASAP and let you know what comes of it. – Bryan Jan 27 '15 at 2:02
  • Totally missed his DISTINCT use, good catch. yah with a GROUP BY its already distinct. – LR1234567 Jan 27 '15 at 2:54
  • 1
    Thanks! FYI, the GiST indexes are what really added a performance boost... your suggested select statement saved ~2 seconds on the 8 core, 30G Google Cloud VM with PostgreSQL 9.4 I spun up to test. The GiST indexes provided an additional ~242 seconds. – Bryan Jan 27 '15 at 3:37
  • @Bryan: Great! Did it work for the plain join or did you have to use the LATERAL join? Added a bit about the subquery, too – Erwin Brandstetter Jan 27 '15 at 11:27

I'm guessing your << is not utilizing an index, because your primary key would create a btree index, and what you need to make that particular operation indexable is a gist index. (use explain analyze to confirm). if that is the case that index not being used,

Try doing something like

   CREATE INDEX idx_network_info_network_gist
     ON network_info USING gist(network inet_ops); 

 CREATE INDEX idx_ipaddr_info_ipaddr_gist
    ON ipaddr_info USING gist(ipaddr inet_ops);

and doing similar with your addr and ipaddr columns.

  • Thanks! If you can, take a look at gist.github.com/activeshadow/2e14bf889d9d216713b6 to see the errors I got when trying to add a gist index for the CIDR and INET types. Ideas? – Bryan Jan 27 '15 at 1:28
  • Weird -- I guess I was able to run the command because I had btree_gist extension installed. It seems even though there is a gist index class, it's not marked as default, so you need to do this. I've ammended my above. – LR1234567 Jan 27 '15 at 2:49
  • I was a bit incorrect in my assessment. I guess that operator can use a btree index though the gist version was introduced in 9.4 and should be better. Here is an article detailing why it's not the default: michael.otacoo.com/postgresql-2/… – LR1234567 Jan 27 '15 at 3:20
  • While you were first to suggest the GiST index, the accepted answer was first to point out the use of inet_ops and the fact that 9.4 would be required. – Bryan Jan 27 '15 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.