Re: Query performance problems with partitioned tables - Mailing list pgsql-performance
From | Fei Liu |
---|---|
Subject | Re: Query performance problems with partitioned tables |
Date | |
Msg-id | 4639F97D.3080003@aepnetworks.com Whole thread Raw |
In response to | Query performance problems with partitioned tables (Andreas Haumer <andreas@xss.co.at>) |
Responses |
Re: Query performance problems with partitioned tables
|
List | pgsql-performance |
Andreas Haumer wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi! > > I'm currently experimenting with PostgreSQL 8.2.4 and table > partitioning in order to improve the performance of an > application I'm working on. > > My application is about managing measurement values (lots of!) > I have one table "t_mv" which stores all the measurement values. > A single measurement value has a timestamp and belongs > to a single time series, so table "t_mv" looks like this: > > CREATE TABLE t_mv > ( > zr integer NOT NULL, -- the time series id > ts timestamp with time zone NOT NULL, -- the timestamp > ... -- other attributes of a mv > ) > WITHOUT OIDS; > > ALTER TABLE t_mv > ADD CONSTRAINT pk_mv_zr_ts PRIMARY KEY (zr, ts); > > Each time series defines several other attributes which are common > to all measurement values of this time series (like sampling location, > physical parameter, aggregation, cardinality, type, visibility, etc.) > > The application should be able to handle several thousand > different time series and hundreds of millions of measurement > values, so table t_mv can get quite large. > > I have tested installations with up to 70 millions rows in t_mv > and PostgreSQL can handle that with a quite good performance > even on non high-end machines (operating system is Linux, btw) > > But as I expect installations witch much more rows in t_mv, I > tried to implement a "partitioned tables" concept using inheritance > and CHECK constraints, just like it is described in the docs > (e.g. chapter 5.9 in the current PostgreSQL 8.2.4 documentation) > > I split the t_mv table on the timestamp attribute to build > child tables which hold all measurement values for a single month. > That way I have several tables called "t_mv_YYYYMM" which all > inherit from "t_mv". The number of child tables depends on the > time period the application has to store the measurement values > (which can be several years so I'm expecting up to 100 child > tables or even more). > For the application everything looks the same: inserts, updates > and queries all are against the "t_mv" parent table, the application > is not aware of the fact that this table is actually "split" into > several child tables. > > This is working fine and for some standard queries it actually > gives some performance improvement compared to the standard > "everything in one big table" concept. The performance improvement > increases with the number of rows in t_mv, for a small table (less > than 10 million rows or so) IMHO it is not really worth the effort > or even counter-productive. > > But I have some special queries where the performance with > partitioned tables actually get much worse: those are queries where > I'm working with "open" time intervals, i.e. where I want to > get the previous and/or next timestamp from a given interval. > > A simple example: Get the timestamp of a measurement value for time > series 3622 which is right before the measurement value with time > stamp '2007-04-22 00:00:00': > > testdb_std=> select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit 1; > ts > - ------------------------ > 2007-04-21 23:00:00+02 > (1 row) > > > Im my application there are many queries like this. Such > queries also come in several variations, including quite > sophisticated joins with lots of other tables "above" the > time series table. > > Note: as I'm working with (potentially) non-equidistant > time series I can not just calculate the timestamps, I > have to retrieve them from the database! > > In the standard case, the query plan for the example query looks like this: > > testdb_std=> explain analyze select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit1; > QUERY PLAN > - ----------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=0.00..1.70 rows=1 width=8) (actual time=0.233..0.235 rows=1 loops=1) > -> Index Scan Backward using pk_mv_zr_ts on t_mv (cost=0.00..21068.91 rows=12399 width=8) (actual time=0.221..0.221rows=1 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > Total runtime: 0.266 ms > (4 rows) > > > If I switch to partitioned tables, the query retrieves the same result (of course): > > testdb_std=> \c testdb_part > You are now connected to database "testdb_part". > testdb_part=> select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit 1; > ts > - ------------------------ > 2007-04-21 23:00:00+02 > (1 row) > > > But the query plan becomes: > > testdb_part=> explain analyze select ts from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' order by ts desc limit1; > QUERY PLAN > - --------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=23985.83..23985.83 rows=1 width=8) (actual time=230.100..230.102 rows=1 loops=1) > -> Sort (cost=23985.83..24019.84 rows=13605 width=8) (actual time=230.095..230.095 rows=1 loops=1) > Sort Key: mwdb.t_mv.ts > -> Result (cost=0.00..23051.72 rows=13605 width=8) (actual time=0.154..177.519 rows=15810 loops=1) > -> Append (cost=0.00..23051.72 rows=13605 width=8) (actual time=0.149..114.186 rows=15810 loops=1) > -> Index Scan using pk_mv_zr_ts on t_mv (cost=0.00..8.27 rows=1 width=8) (actual time=0.047..0.047rows=0 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200507 on t_mv_200507 t_mv (cost=0.00..2417.53 rows=1519 width=8) (actualtime=0.095..2.419 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200508 on t_mv_200508 t_mv (cost=0.00..918.81 rows=539 width=8) (actualtime=0.081..2.134 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200509 on t_mv_200509 t_mv (cost=0.00..941.88 rows=555 width=8) (actualtime=0.061..2.051 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200510 on t_mv_200510 t_mv (cost=0.00..915.29 rows=538 width=8) (actualtime=0.064..2.113 rows=715 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200511 on t_mv_200511 t_mv (cost=0.00..925.93 rows=545 width=8) (actualtime=0.048..2.986 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200512 on t_mv_200512 t_mv (cost=0.00..936.53 rows=550 width=8) (actualtime=0.049..2.212 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200601 on t_mv_200601 t_mv (cost=0.00..981.42 rows=579 width=8) (actualtime=0.065..3.029 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200602 on t_mv_200602 t_mv (cost=0.00..856.25 rows=502 width=8) (actualtime=0.045..2.866 rows=672 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200603 on t_mv_200603 t_mv (cost=0.00..977.84 rows=575 width=8) (actualtime=0.052..3.044 rows=743 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200604 on t_mv_200604 t_mv (cost=0.00..906.40 rows=531 width=8) (actualtime=0.053..1.976 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200605 on t_mv_200605 t_mv (cost=0.00..938.28 rows=550 width=8) (actualtime=0.050..2.357 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200606 on t_mv_200606 t_mv (cost=0.00..922.35 rows=541 width=8) (actualtime=0.054..2.063 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200607 on t_mv_200607 t_mv (cost=0.00..2112.64 rows=1315 width=8) (actualtime=0.047..2.226 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200608 on t_mv_200608 t_mv (cost=0.00..990.23 rows=582 width=8) (actualtime=0.048..2.094 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200609 on t_mv_200609 t_mv (cost=0.00..902.84 rows=528 width=8) (actualtime=0.039..2.252 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200610 on t_mv_200610 t_mv (cost=0.00..964.87 rows=567 width=8) (actualtime=0.033..2.118 rows=745 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200611 on t_mv_200611 t_mv (cost=0.00..947.17 rows=557 width=8) (actualtime=0.060..2.160 rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200612 on t_mv_200612 t_mv (cost=0.00..929.43 rows=545 width=8) (actualtime=0.039..2.051 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200701 on t_mv_200701 t_mv (cost=0.00..940.05 rows=551 width=8) (actualtime=0.036..2.217 rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200702 on t_mv_200702 t_mv (cost=0.00..847.38 rows=496 width=8) (actualtime=0.035..1.830 rows=672 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200703 on t_mv_200703 t_mv (cost=0.00..956.00 rows=561 width=8) (actualtime=0.062..2.326 rows=743 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200704 on t_mv_200704 t_mv (cost=0.00..814.38 rows=378 width=8) (actualtime=0.050..1.406 rows=504 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > Total runtime: 231.730 ms > (52 rows) > > Oops! > Compare the costs or the actual query time between those queries! > (Note: I set "constraint_exclusion = on", of course!) > > As such queries are used all over the application, this nullifies > any performance improvements for standard queries and in fact makes > the overall application performance as "feeled" by the user _much_ > worse. > > I also tried it with "min()" and "max()" aggregate functions > instead of the "limit 1" query, but this does not change much: > > > Standard "big" table: > > testdb_std=> select max(ts) from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' ; > max > - ------------------------ > 2007-04-21 23:00:00+02 > (1 row) > > > testdb_std=> explain analyze select max(ts) from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' ; > QUERY PLAN > - ------------------------------------------------------------------------------------------------------------------------------------------------- > Result (cost=1.70..1.71 rows=1 width=0) (actual time=0.071..0.073 rows=1 loops=1) > InitPlan > -> Limit (cost=0.00..1.70 rows=1 width=8) (actual time=0.060..0.062 rows=1 loops=1) > -> Index Scan Backward using pk_mv_zr_ts on t_mv (cost=0.00..21068.91 rows=12399 width=8) (actual time=0.056..0.056rows=1 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > Filter: ((ts)::timestamp with time zone IS NOT NULL) > Total runtime: 0.221 ms > (7 rows) > > > "Partitioned table": > > testdb_part=> select max(ts) from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' ; > max > - ------------------------ > 2007-04-21 23:00:00+02 > (1 row) > > testdb_part=> explain analyze select max(ts) from mwdb.t_mv where zr=3622 and ts < '2007-04-22 00:00:00' ; > QUERY PLAN > - ---------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=23085.73..23085.74 rows=1 width=8) (actual time=390.094..390.096 rows=1 loops=1) > -> Append (cost=0.00..23051.72 rows=13605 width=8) (actual time=0.241..290.934 rows=15810 loops=1) > -> Index Scan using pk_mv_zr_ts on t_mv (cost=0.00..8.27 rows=1 width=8) (actual time=0.038..0.038 rows=0 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200507 on t_mv_200507 t_mv (cost=0.00..2417.53 rows=1519 width=8) (actual time=0.197..12.598rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200508 on t_mv_200508 t_mv (cost=0.00..918.81 rows=539 width=8) (actual time=0.095..5.947rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200509 on t_mv_200509 t_mv (cost=0.00..941.88 rows=555 width=8) (actual time=0.118..2.247rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200510 on t_mv_200510 t_mv (cost=0.00..915.29 rows=538 width=8) (actual time=0.121..6.219rows=715 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200511 on t_mv_200511 t_mv (cost=0.00..925.93 rows=545 width=8) (actual time=2.287..9.991rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200512 on t_mv_200512 t_mv (cost=0.00..936.53 rows=550 width=8) (actual time=0.110..2.285rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200601 on t_mv_200601 t_mv (cost=0.00..981.42 rows=579 width=8) (actual time=0.209..4.682rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200602 on t_mv_200602 t_mv (cost=0.00..856.25 rows=502 width=8) (actual time=0.079..6.079rows=672 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200603 on t_mv_200603 t_mv (cost=0.00..977.84 rows=575 width=8) (actual time=0.091..4.793rows=743 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200604 on t_mv_200604 t_mv (cost=0.00..906.40 rows=531 width=8) (actual time=0.108..7.637rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200605 on t_mv_200605 t_mv (cost=0.00..938.28 rows=550 width=8) (actual time=0.116..4.772rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200606 on t_mv_200606 t_mv (cost=0.00..922.35 rows=541 width=8) (actual time=0.074..6.071rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200607 on t_mv_200607 t_mv (cost=0.00..2112.64 rows=1315 width=8) (actual time=0.082..4.807rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200608 on t_mv_200608 t_mv (cost=0.00..990.23 rows=582 width=8) (actual time=2.283..8.671rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200609 on t_mv_200609 t_mv (cost=0.00..902.84 rows=528 width=8) (actual time=0.107..6.067rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200610 on t_mv_200610 t_mv (cost=0.00..964.87 rows=567 width=8) (actual time=0.074..3.933rows=745 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200611 on t_mv_200611 t_mv (cost=0.00..947.17 rows=557 width=8) (actual time=0.091..6.291rows=720 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200612 on t_mv_200612 t_mv (cost=0.00..929.43 rows=545 width=8) (actual time=0.077..4.101rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200701 on t_mv_200701 t_mv (cost=0.00..940.05 rows=551 width=8) (actual time=0.077..2.558rows=744 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200702 on t_mv_200702 t_mv (cost=0.00..847.38 rows=496 width=8) (actual time=0.073..4.346rows=672 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200703 on t_mv_200703 t_mv (cost=0.00..956.00 rows=561 width=8) (actual time=2.532..7.206rows=743 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > -> Index Scan using pk_mv_200704 on t_mv_200704 t_mv (cost=0.00..814.38 rows=378 width=8) (actual time=0.120..4.163rows=504 loops=1) > Index Cond: (((zr)::integer = 3622) AND ((ts)::timestamp with time zone < '2007-04-22 00:00:00+02'::timestampwith time zone)) > Total runtime: 394.384 ms > (49 rows) > > > Now my question is: Does the query planner in the case of partitioned tables > really have to scan all indexes in order to get the next timestamp smaller > (or larger) than a given one? > > There are check conditions on all table partitions like this: > > For table t_mv_200704: > CHECK (ts::timestamp with time zone >= '2007-04-01 00:00:00+02'::timestamp with time zone > AND ts::timestamp with time zone < '2007-05-01 00:00:00+02'::timestamp with time zone) > > For table t_mv_200703: > CHECK (ts::timestamp with time zone >= '2007-03-01 00:00:00+01'::timestamp with time zone > AND ts::timestamp with time zone < '2007-04-01 00:00:00+02'::timestamp with time zone) > > and so on... > > So the tables are in a well defined, monotonic sort order regarding the timestamp. > > This means that if there is a max(ts) for ts < '2007-04-22 00:00:00' > already in table t_mv_200704, it makes no sense to look further in > other tables where the timestamps can only be smaller than the > timestamp already found. Am I correct? > > Is there room for improvements of the query planner for queries > like this or is this a special case which will never get handled > anyway? > > Or would you suggest a completely different table structure > or perhaps some other query? > > I'm open for any suggestion! > > - - andreas > > - -- > Andreas Haumer | mailto:andreas@xss.co.at > *x Software + Systeme | http://www.xss.co.at/ > Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0 > A-1100 Vienna, Austria | Fax: +43-1-6060114-71 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFGNdZ2xJmyeGcXPhERAsbfAJ9nA+z50uXiV4SHntt1Y9IuZ/rzWwCff8ar > xKSMfzwgjx9kQipeDoEnXWE= > =57aJ > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > Hello, Andreas, I too am having exactly the same issue as you do. Comparing my partitioned and plain table performance, I've found that the plain tables perform about 25% faster than partitioned table. Using 'explain select ...', I see that constraints are being used so in partitioned tables fewer rows are examined. But still partitioned tables are 25% slower, what a let down. Fei
pgsql-performance by date: