I have a "master table" called events, and 10 subtables which are
created using this:
CREATE TABLE events001 ( ) INHERITS (events) WITHOUT OIDS;
I then build all the indexes on it, including a column called "src_ip":
CREATE INDEX events001_src_ip_idx ON events001(src_ip);
Then I populate it with about 1M events per table, but none in the
"master table". I'm using this for data partitioning basically.
When I do a select count(event_id) from events... that does multiple
sequential table scans, as expected. However, when I do:
SELECT event_id FROM events WHERE src_ip = 3232235511;
It claims to use indexes, but ti takes over 90 seconds to perform this
(on a Athlong64 box, with 10K RPM SATA drive). This is after a VACUUM
ANALYZE as well. The EXPLAIN results are here:
bench2=# explain select count(event_id) from events where src_ip = 3232235512;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=113151.90..113151.90 rows=1 width=66)
-> Append (cost=0.00..113078.85 rows=29218 width=66)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=66)
Filter: (src_ip = 3232235512::bigint)
-> Index Scan using events001_src_ip_idx on events001 events
(cost=0.00..11323.63 rows=2925 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events002_src_ip_idx on events002 events
(cost=0.00..11365.13 rows=2937 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events003_src_ip_idx on events003 events
(cost=0.00..11276.54 rows=2912 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events004_src_ip_idx on events004 events
(cost=0.00..11222.15 rows=2898 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events005_src_ip_idx on events005 events
(cost=0.00..11377.30 rows=2946 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events006_src_ip_idx on events006 events
(cost=0.00..11365.05 rows=2935 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events007_src_ip_idx on events007 events
(cost=0.00..11309.66 rows=2922 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events008_src_ip_idx on events008 events
(cost=0.00..11371.45 rows=2937 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events009_src_ip_idx on events009 events
(cost=0.00..11244.08 rows=2905 width=36)
Index Cond: (src_ip = 3232235512::bigint)
-> Index Scan using events010_src_ip_idx on events010 events
(cost=0.00..11223.85 rows=2900 width=36)
Index Cond: (src_ip = 3232235512::bigint)
(24 rows)
bench2=# select count(event_id) from events where src_ip = 3232235512;
count
-------
40198
(1 row)
If you do it against the derived tables it takes a while as well
(roughly 1/10th as long).
Any thoughts for where i'm missing something here? I would think an
index lookup that is an equality, even with 1M entries, wouldn't take
more than 1/2 second.
Chris
--
| Christopher Petrilli
| petrilli@xxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
