Hi.
I have a query that should be very fast because it's using all
indexes, but it takes a lot of time.
explain analyze select * from sites where user_id = 'phoenix' order by
id desc limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=344.85..344.87 rows=10 width=262) (actual
time=5879.069..5879.167 rows=10 loops=1)
-> Sort (cost=344.85..345.66 rows=323 width=262) (actual
time=5879.060..5879.093 rows=10 loops=1)
Sort Key: id
-> Index Scan using new_idx_sites_userid on sites
(cost=0.00..331.39 rows=323 width=262) (actual time=44.408..5867.557
rows=2178 loops=1)
Index Cond: ((user_id)::text = 'phoenix'::text)
Total runtime: 5879.414 ms
(6 rows)
Time: 5885.928 ms
This query should really not be taking 6 seconds!
So my theories:
1. Somehow the sorting is taking a lot of time. Even though it's on
the primary key, to find "id desc" the query has to see all the rows.
2. Or the vacuums that I am doing are not very efficient? (Autovacuum
is on, and there's nothing in the pg log!)
3. Or the shared buffer or other settings that I have are not up to the mark?
I am on CentOS, running PG 8.2.9, with 4GB of RAM. The hard disk is
SATA II. All other queries are pretty fast, and this query repeated
with different offsets is also very fast, perhaps because the results
are cached?
My PG conf settings are:
#==================
max_connections = 300
shared_buffers = 330MB # Not much more than
20k...http://www.revsys.com/writigs/postgresql-performance.html
effective_cache_size = 512000 #
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
max_fsm_relations = 100
max_fsm_pages = 300000
work_mem = 20MB
temp_buffers = 4096
authentication_timeout = 10s
ssl = off
checkpoint_warning = 3600
random_page_cost = 1
autovacuum = on
autovacuum_vacuum_cost_delay = 20
vacuum_cost_delay = 20
vacuum_cost_limit = 600 # http://sn.im/b86nd - Vacuums
taking forever
autovacuum_naptime = 10
stats_start_collector = on
stats_row_level = on
autovacuum_vacuum_threshold = 75
autovacuum_analyze_threshold = 25
#==================
And my table is as follows:
#==================
Table "public.sites"
Column | Type |
Modifiers
-----------------------+-----------------------------+------------------------------
id | bigint | not null
link_id | character varying(10) | not null
alias | character varying(35) | not null
aliasentered | character(1) | default 'N'::bpchar
url | text | not null
user_known | smallint | not null default 0
user_id | character varying(45) | not null
url_encrypted | character(40) | default ''::bpchar
title | character varying(500) |
private | character(1) |
private_key | character varying(6) |
status | character(1) | default 'Y'::bpchar
create_date | timestamp without time zone | default now()
modify_date | timestamp without time zone |
disable_in_statistics | character(1) | not null
default 'N'::bpchar
Indexes:
"sites2_pkey" PRIMARY KEY, btree (id)
"sites2_alias_key" UNIQUE, btree (alias) WITH (fillfactor=75)
"idx_unique_user_urlenc" btree (user_id, url_encrypted) WITH (fillfactor=70)
"new_idx_sites_userid" btree (user_id) WITH (fillfactor=70)
"new_idx_modify_date" btree (modify_date) WITH (fillfactor=75)
"new_idx_userknown" btree (user_id) WITH (fillfactor=70) WHERE
user_known = 1
Check constraints:
"sites2_id_check" CHECK (id > 0)
"sites2_url_check" CHECK (url <> ''::text)
"sites2_user_id_check" CHECK (user_id::text <> ''::text)
"sites_alias_check" CHECK (alias::text ~ '[-.~a-z0-9_]'::text)
#==================
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
