PostgreSQL slow COUNT() - is trigger the only solution? -

-

i have table posts, categorized by:

  • type
  • tag
  • language

all of "categories" stored in next tables (posts_types) , connected via next tables (posts_types_assignment).

counting in postgresql slow (i have more 500k records in table) , need number of posts categorized combination of type/tag/lang.

if solve through triggers, full of many multi-level loops, doesn't nice , hard maintenance.

is there other solution how actual number of posts categorized in type/tag/language?

let me straight.

you have table posts. have table posts_types. 2 have many many join on posts_types_assignment. , have query slow:

select count(*) posts p   join posts_types_assigment pta1     on p.id = pta1.post_id   join posts_types pt1     on pt1.id = pta1.post_type_id       , pt1.type = 'language'       , pt1.name = 'english'   join posts_types_assigment pta2     on p.id = pta2.post_id   join posts_types pt2     on pt2.id = pta2.post_type_id       , pt2.type = 'tag'       , pt2.name = 'awesome'

and know why painfully slow.

my first note postgresql have lot less work if had identifiers in posts table rather in joins. moot issue, decision has been made.

my more useful note believe postgresql has similar query optimizer oracle. in case limit combinatorial explosion of possible query plans has consider, considers plans start table, , repeatedly joins on 1 more data set @ time. no such query plan work here. can start pt1, 1 record, go pta1, bunch of records, join p, wind same number of records, join pta2, , huge number of records, join pt2, few records. joining pta2 slow step, because database has no idea records want, , therefore has create temporary result set every combination of post , piece of metadata (type, language or tag) on it.

if indeed problem, right plan looks this. join pt1 pta1, put index on it. join pt2 pta2, join result of first query, join p. count. means don't huge result sets.

if case, there no way tell query optimizer once want think new type of execution plan. there way force it.

create temporary table t1 select pta* posts_types pt   join posts_types_assignment pta     on pt.id = pta.post_type_id pt.type = 'language'   , pt.name = 'english';  create index idx1 on t1 (post_id);  create temporary table t2 select pta* posts_types pt   join posts_types_assignment pta     on pt.id = pta.post_type_id   join t1     on t1.post_id = pta.post_id pt.type = 'language'   , pt.name = 'english';  select count(*) posts p   join t1     on p.id = t1.post_id;

barring random typos, etc, perform better. if doesn't, double check indexes on tables.


Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more