Re: [PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance => Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226
On Tue, May 3, 2011 at 10:02 AM, Sethu Prasad wrote: > I tried with the PostgreSQL 9.0.4 + Hot Standby and running the database > from Fusion IO Drive to understand the PG Performance. > > While doing so I got the "Query failed ERROR: catalog is missing 1 > attribute(s) for relid 172226". Any idea on this error? Is that combination > PG + HotSB + Fusion IO Drive is not advisable?! Why I wonder do you think this might have anything to do with Hot Standby and/or FusionIO drives? This indicates either catalog or catalog index corruption of some kind. Did you only get this error once? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Query improvement
Thanks for replies. Finally I have used UNION and JOINS, which helped. Mainly the UNION helped a lot. Now the query takes 1sec max. Thanks a lot. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-improvement-tp4362578p4378163.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] indexes ignored when querying the master table
i have around 25mio records of data distributed yearly over 9 child
tables (data.logs_20xx) that inherit from the master table data.logs.
the tables are partitioned using the field "re_timestamp", which has
btree indexes defined on all tables.
the query "SELECT * FROM data.logs ORDER BY re_timestamp DESC LIMIT 100"
does use seq scans on all tables instead of using the existing indexes
which takes ages. when issuing the the same query to one of the child
tables directly ("SELECT * FROM data.logs_2011 ORDER BY re_timestamp
DESC LIMIT 100") the index is used as expected and the data returned
quickly.
how can i get postgres to use the indexes when querying the master table?
please find below the EXPLAIN ANALYZE output for both queries on my
development machine (pgsql 9.0 x64 on windows 7).
thanks in advance,
thomas
EXPLAIN ANALYZE SELECT * FROM data.logs
ORDER BY re_timestamp DESC LIMIT 100;
Limit (cost=6331255.90..6331256.15 rows=100 width=1388) (actual
time=1592287.794..1592287.808 rows=100 loops=1)
-> Sort (cost=6331255.90..6395928.37 rows=25868986 width=1388)
(actual time=1592287.789..1592287.796 rows=100 loops=1)
Sort Key: data.logs.re_timestamp
Sort Method: top-N heapsort Memory: 217kB
-> Result (cost=0.00..5342561.86 rows=25868986 width=1388)
(actual time=0.026..1466420.868 rows=25870101 loops=1)
-> Append (cost=0.00..5342561.86 rows=25868986
width=1388) (actual time=0.020..1417490.892 rows=25870101 loops=1)
-> Seq Scan on logs (cost=0.00..10.40 rows=40
width=1776) (actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on logs_2011 logs
(cost=0.00..195428.00 rows=904800 width=1449) (actual
time=0.017..92381.769 rows=904401 loops=1)
-> Seq Scan on logs_2010 logs
(cost=0.00..759610.67 rows=3578567 width=1426) (actual
time=23.996..257612.143 rows=3579586 loops=1)
-> Seq Scan on logs_2009 logs
(cost=0.00..841998.35 rows=3987235 width=1423) (actual
time=12.921..200385.903 rows=3986473 loops=1)
-> Seq Scan on logs_2008 logs
(cost=0.00..942810.60 rows=4409860 width=1444) (actual
time=18.861..226867.499 rows=4406653 loops=1)
-> Seq Scan on logs_2007 logs
(cost=0.00..730863.69 rows=3600569 width=1359) (actual
time=14.406..174082.413 rows=3603739 loops=1)
-> Seq Scan on logs_2006 logs
(cost=0.00..620978.29 rows=3089929 width=1348) (actual
time=21.647..147244.677 rows=3091214 loops=1)
-> Seq Scan on logs_2005 logs
(cost=0.00..486928.59 rows=2440959 width=1342) (actual
time=0.005..126479.314 rows=2438968 loops=1)
-> Seq Scan on logs_2004 logs
(cost=0.00..402991.92 rows=2031092 width=1327) (actual
time=23.007..98531.883 rows=2034041 loops=1)
-> Seq Scan on logs_2003 logs
(cost=0.00..360941.35 rows=1825935 width=1325) (actual
time=20.220..91773.705 rows=1825026 loops=1)
Total runtime: 1592293.267 ms
EXPLAIN ANALYZE SELECT * FROM data.logs_2011
ORDER BY re_timestamp DESC LIMIT 100;
Limit (cost=0.00..22.65 rows=100 width=1449) (actual
time=59.161..60.226 rows=100 loops=1)
-> Index Scan Backward using logs_fts_2011_timestamp_idx on
logs_2011 (cost=0.00..204919.30 rows=904800 width=1449) (actual
time=59.158..60.215 rows=100 loops=1)
Total runtime: 60.316 ms
--
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] Query improvement
Thanks for reply both UNION and JOINS helped. Mainly the UNION helped a lot. Now the query takes 1sec max. Thanks a lot. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-improvement-tp4362578p4378157.html Sent from the PostgreSQL - performance mailing list archive at Nabble.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] Query improvement
Thanks a lot for reply. Finally I have used UNION, but thanks for your help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-improvement-tp4362578p4378160.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
