Hello pgsql-performance,
I discussed the whole subject for some time in DevShed and didn't achieve much (as for results). I wonder if any of you guys can help out:
http://forums.devshed.com/t136202/s.html
So cutting and pasting:
----- SCHEMA -----
CREATE TABLE bv_bookgenres (
book_id INT NOT NULL,
genre_id INT NOT NULL
);
CREATE TABLE bv_genre_children (
genre_id INT,
genre_child_id INT
);
------------------------ QUERY -----
select DISTINCT
book_id
from
bookgenres,
genre_children
WHERE
bookgenres.genre_id = genre_children.genre_child_id AND
genre_children.genre_id = 1
LIMIT 10
---------------------- EXPLAIN ANALYZE -----
QUERY PLAN
Limit (cost=6503.51..6503.70 rows=10 width=4) (actual time=703.000..703.000 rows=10 loops=1)
-> Unique (cost=6503.51..6738.20 rows=12210 width=4) (actual time=703.000..703.000 rows=10 loops=1)
-> Sort (cost=6503.51..6620.85 rows=46937 width=4) (actual time=703.000..703.000 rows=24 loops=1)
Sort Key: bv_bookgenres.book_id
-> Merge Join (cost=582.45..2861.57 rows=46937 width=4) (actual time=46.000..501.000 rows=45082 loops=1)
Merge Cond: ("outer".genre_id = "inner".genre_child_id)
-> Index Scan using genre_id on bv_bookgenres (cost=0.00..1462.84 rows=45082 width=8) (actual time=0.000..158.000 rows=45082 loops=1)
-> Sort (cost=582.45..598.09 rows=6256 width=2) (actual time=46.000..77.000 rows=49815 loops=1)
Sort Key: bv_genre_children.genre_child_id
-> Index Scan using genre_id2 on bv_genre_children (cost=0.00..187.98 rows=6256 width=2) (actual time=0.000..31.000 rows=6379 loops=1)
Index Cond: (genre_id = 1)
Total runtime: 703.000 ms
-------------------------------
----- CONF SETTINGS ----- shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each sort_mem = 10000 #work_mem = 1024 # min 64, size in KB #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB -------------------------
Have you VACUUM ANALYZED recently. If not do that then rerun the EXPLAIN ANALYZE.
You might wanna bump shared_buffers. You have 512MB RAM right? You probably want to bump shared_buffers to 10000, restart PG then run a VACUUM ANALYZE. Then rerun the EXPLAIN ANALYZE.
If that doesnt help try doing a
ALTER TABLE bv_genre_children ALTER COLUMN genre_child_id SET STATISTICS 100;
followed by a:
VACUUM ANALYZE bv_genre_children;
You might also want to be tweaking the effective_cache_size parameter in postgresql.conf, but I am unsure how this would work on Windows. Does Windows have a kernel disk cache anyone?
HTH
Nick
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
