Re: [PERFORM] planner index choice
http://explain.depesz.com/s/br9 http://explain.depesz.com/s/gxH Well, I don't have time to do a thorough analysis right now, but in all the plans you've posted there are quite high values in the Rows x column (e.g. the 5727.5 value). That means a significant difference in estimated and actual row number, which may lead to poor choice of indexes etc. The planner may simply think the index is better due to imprecise statistics etc. Try to increase te statistics target for the columns, e.g. ALTER TABLE table ALTER COLUMN column SET STATISTICS integer where integer is between 0 and 1000 (the default value is 10 so use 100 or maybe 1000), run analyze and try to run the query again. Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] planner index choice
t...@fuzzy.cz writes: http://explain.depesz.com/s/br9 http://explain.depesz.com/s/gxH Well, I don't have time to do a thorough analysis right now, but in all the plans you've posted there are quite high values in the Rows x column (e.g. the 5727.5 value). That means a significant difference in estimated and actual row number, which may lead to poor choice of indexes etc. The planner may simply think the index is better due to imprecise statistics etc. Yeah. The sq_ast_attr_val_attrid scan is a lot more selective than the planner is guessing (3378 rows estimated vs an average of 15 actual), and I think that is making the difference. If you look at the estimated row counts and costs, it's expecting that adding the second index will cut the number of heap fetches about 7x, hence saving somewhere around 4800 cost units in the heapscan step, more than it thinks the indexscan will cost. But taking 15 row fetches down to 2 isn't nearly enough to pay for the extra indexscan. Try to increase te statistics target for the columns, e.g. ALTER TABLE table ALTER COLUMN column SET STATISTICS integer It's worth a try but I'm not sure how much it'll help. A different line of attack is to play with the planner cost parameters. In particular, reducing random_page_cost would reduce the estimated cost of the heap fetches and thus discourage it from using the extra index. If you're working with mostly-cached tables then this would probably improve behavior overall, too. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] planner index choice
Hi there,
I have a simple query where I don't understand the planner's choice to
use a particular index.
The main table looks like this:
# \d sq_ast_attr_val
Table public.sq_ast_attr_val
Column| Type | Modifiers
-+---+--
assetid | character varying(15) | not null
attrid | integer | not null
contextid | integer | not null default 0
custom_val | text |
use_default | character(1) | not null default '1'::bpchar
Indexes:
ast_attr_val_pk PRIMARY KEY, btree (assetid, attrid, contextid)
sq_ast_attr_val_assetid btree (assetid)
sq_ast_attr_val_attrid btree (attrid)
sq_ast_attr_val_concat btree (((assetid::text || '~'::text) ||
attrid))
sq_ast_attr_val_contextid btree (contextid)
The query:
SELECT
assetid, custom_val
FROM
sq_ast_attr_val
WHERE
attrid IN (SELECT attrid FROM sq_ast_attr WHERE name =
'is_contextable' AND (type_code = 'metadata_field_select' OR
owning_type_code = 'metadata_field'))
AND contextid = 0
INTERSECT
SELECT
assetid, custom_val
FROM
sq_ast_attr_val
WHERE
assetid = '62321'
AND contextid = 0;
The explain analyze plan:
http://explain.depesz.com/s/nWs
I'm not sure why it's picking the sq_ast_attr_val_contextid index to do
the contextid = 0 check, the other parts (attrid/assetid) are much more
selective.
If I drop that particular index:
http://explain.depesz.com/s/zp
All (I hope) relevant postgres info:
Centos 5.5 x86_64 running pg8.4.4.
Server has 8gig memory.
# select name, setting, source from pg_settings where name in
('shared_buffers', 'effective_cache_size', 'work_mem');
name | setting
--+
shared_buffers| 262144
effective_cache_size | 655360
work_mem | 32768
All planner options are enabled:
# select name, setting, source from pg_settings where name like 'enable_%';
name| setting | source
---+-+-
enable_bitmapscan | on | default
enable_hashagg| on | default
enable_hashjoin | on | default
enable_indexscan | on | default
enable_mergejoin | on | default
enable_nestloop | on | default
enable_seqscan| on | default
enable_sort | on | default
enable_tidscan| on | default
Any insights welcome - thanks!
--
Postgresql php tutorials
http://www.designmagick.com/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] planner index choice
Chris dmag...@gmail.com writes: The query: SELECT assetid, custom_val FROM sq_ast_attr_val WHERE attrid IN (SELECT attrid FROM sq_ast_attr WHERE name = 'is_contextable' AND (type_code = 'metadata_field_select' OR owning_type_code = 'metadata_field')) AND contextid = 0 INTERSECT SELECT assetid, custom_val FROM sq_ast_attr_val WHERE assetid = '62321' AND contextid = 0; The explain analyze plan: http://explain.depesz.com/s/nWs Hrm ... are you *certain* that's an 8.4 server? Because the bit with Index Cond: (sq_ast_attr_val.attrid = outer.attrid) is a locution that EXPLAIN hasn't used since 8.1, according to a quick check. More recent versions don't say outer. The actual problem seems to be that choose_bitmap_and() is choosing to add an indexscan on sq_ast_attr_val_contextid, even though this index is a lot less selective than the sq_ast_attr_val_attrid scan it had already picked. I've seen that behavior before, and there were a series of patches back in 2006-2007 that seem to have pretty much fixed it. So that's another reason for suspecting you've got an old server version there... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
