Re: [PERFORM] [NOVICE] error while executing a c program with embedded sql
Thanks a lot. IT WORKED! with your suggestions. Regards, Radha On Sun, 2003-11-09 at 15:06, [EMAIL PROTECTED] wrote: I have a c program called test1.pgc with some sql statements embedded in it. The program was preprocessed, compiled and linked. Now, I have the executable test1. When I run the executable it says, ./test1: error while loading shared libraries: libecpg.so.3: cannot open shared object file: No such file or directory What does it mean by this error message? What should I do to correct this error and run the executable successfully? Shared libraries are loaded from directories specified to the system by ldconfig. Your shared library, libecpg.so.3, is in a PostgreSQL directory, such as /usr/local/pgsql/lib, which has not been added to the directories known to the loader. If you are able to add that directory with ldconfig, that is the best way to do it, but it requires root privilege. Otherwise you can set the environment variable LD_LIBRARY_PATH, thus: export LD_LIBRARY_PATH=/usr/local/pgsql/lib before you run the program, or you can use LD_PRELOAD: LD_PRELOAD=/usr/local/pgsql/lib/libecpg.so.3 ./test1 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C O death, where is thy sting? O grave, where is thy victory? 1 Corinthians 15:55 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] *very* slow query to summarize data for a month ...
Table structure is simple:
CREATE TABLE traffic_logs (
company_id bigint,
ip_id bigint,
port integer,
bytes bigint,
runtime timestamp without time zone
);
runtime is 'day of month' ...
I need to summarize the month, per company, with a query as:
explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic
FROM company c, traffic_logs ts
WHERE c.company_id = ts.company_id
AND month_trunc(ts.runtime) = '2003-10-01'
GROUP BY company_name,ts.company_id;
and the explain looks like:
QUERY
PLAN
--
Aggregate (cost=32000.94..32083.07 rows=821 width=41) (actual
time=32983.36..47586.17 rows=144 loops=1)
- Group (cost=32000.94..32062.54 rows=8213 width=41) (actual
time=32957.40..42817.88 rows=462198 loops=1)
- Sort (cost=32000.94..32021.47 rows=8213 width=41) (actual
time=32957.38..36261.31 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
- Merge Join (cost=31321.45..31466.92 rows=8213 width=41) (actual
time=13983.07..22642.14 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=5.52..7.40 rows=348 loops=1)
Sort Key: c.company_id
- Seq Scan on company c (cost=0.00..9.52 rows=352
width=25) (actual time=0.02..2.78 rows=352 loops=1)
- Sort (cost=31297.04..31317.57 rows=8213 width=16) (actual
time=13977.49..16794.41 rows=462198 loops=1)
Sort Key: ts.company_id
- Index Scan using tl_month on traffic_logs ts
(cost=0.00..30763.02 rows=8213 width=16) (actual time=0.29..5562.25 rows=462198
loops=1)
Index Cond: (month_trunc(runtime) = '2003-10-01
00:00:00'::timestamp without time zone)
Total runtime: 47587.82 msec
(14 rows)
the problem is that we're only taking a few months worth of data, so I
don't think there is much of a way of 'improve performance' on this, but
figured I'd ask quickly before I do something rash ...
Note that without the month_trunc() index, the Total runtime more then
doubles:
QUERY PLAN
Aggregate (cost=39578.63..39660.76 rows=821 width=41) (actual
time=87805.47..101251.35 rows=144 loops=1)
- Group (cost=39578.63..39640.23 rows=8213 width=41) (actual
time=87779.56..96824.56 rows=462198 loops=1)
- Sort (cost=39578.63..39599.17 rows=8213 width=41) (actual
time=87779.52..90781.48 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
- Merge Join (cost=38899.14..39044.62 rows=8213 width=41) (actual
time=64073.98..72783.68 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=64.66..66.55 rows=348 loops=1)
Sort Key: c.company_id
- Seq Scan on company c (cost=0.00..9.52 rows=352
width=25) (actual time=1.76..61.70 rows=352 loops=1)
- Sort (cost=38874.73..38895.27 rows=8213 width=16) (actual
time=64009.26..66860.71 rows=462198 loops=1)
Sort Key: ts.company_id
- Seq Scan on traffic_logs ts (cost=0.00..38340.72
rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) =
'2003-10-01 00:00:00'::timestamp without time zone)
Total runtime: 101277.17 msec
(14 rows)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] *very* slow query to summarize data for a month ...
here's the URL: http://techdocs.postgresql.org/techdocs/pgsqladventuresep2.php Patrick Hatcher Macys.Com Legacy Integration Developer 415-422-1610 office HatcherPT - AIM Patrick Hatcher/MCOM/FDD To 11/10/2003 12:31 PMMarc G. Fournier [EMAIL PROTECTED]@FDS-NOTES cc [EMAIL PROTECTED], [EMAIL PROTECTED] rg Subject Re: [PERFORM] *very* slow query to summarize data for a month ... (Document link: Patrick Hatcher) Do you have an index on ts.bytes? Josh had suggested this and after I put it on my summed fields, I saw a speed increase. I can't remember the article was that Josh had written about index usage, but maybe he'll chime in and supply the URL for his article. hth Patrick Hatcher Marc G. Fournier [EMAIL PROTECTED] .orgTo Sent by: [EMAIL PROTECTED] pgsql-performance-o cc [EMAIL PROTECTED] Subject [PERFORM] *very* slow query to 11/10/2003 12:18 PMsummarize data for a month ... Table structure is simple: CREATE TABLE traffic_logs ( company_id bigint, ip_id bigint, port integer, bytes bigint, runtime timestamp without time zone ); runtime is 'day of month' ... I need to summarize the month, per company, with a query as: explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic FROM company c, traffic_logs ts WHERE c.company_id = ts.company_id AND month_trunc(ts.runtime) = '2003-10-01' GROUP BY company_name,ts.company_id; and the explain looks like: QUERY PLAN -- Aggregate (cost=32000.94..32083.07 rows=821 width=41) (actual time=32983.36..47586.17 rows=144 loops=1) - Group (cost=32000.94..32062.54 rows=8213 width=41) (actual time=32957.40..42817.88 rows=462198 loops=1) - Sort (cost=32000.94..32021.47 rows=8213 width=41) (actual time=32957.38..36261.31 rows=462198 loops=1) Sort Key: c.company_name, ts.company_id - Merge Join (cost=31321.45..31466.92 rows=8213 width=41) (actual time=13983.07..22642.14 rows=462198 loops=1) Merge Cond: (outer.company_id = inner.company_id) - Sort (cost=24.41..25.29 rows=352 width=25) (actual time=5.52..7.40 rows=348 loops=1) Sort Key: c.company_id - Seq Scan on company c (cost=0.00..9.52 rows=352 width=25) (actual time=0.02..2.78 rows=352 loops=1) - Sort (cost=31297.04..31317.57 rows=8213 width=16) (actual time=13977.49..16794.41 rows=462198 loops=1) Sort Key: ts.company_id - Index Scan using tl_month on traffic_logs ts
Re: [PERFORM] *very* slow query to summarize data for a month ...
Do you have an index on ts.bytes? Josh had suggested this and after I put it on my summed fields, I saw a speed increase. I can't remember the article was that Josh had written about index usage, but maybe he'll chime in and supply the URL for his article. hth Patrick Hatcher Marc G. Fournier [EMAIL PROTECTED] .orgTo Sent by: [EMAIL PROTECTED] pgsql-performance-o cc [EMAIL PROTECTED] Subject [PERFORM] *very* slow query to 11/10/2003 12:18 PMsummarize data for a month ... Table structure is simple: CREATE TABLE traffic_logs ( company_id bigint, ip_id bigint, port integer, bytes bigint, runtime timestamp without time zone ); runtime is 'day of month' ... I need to summarize the month, per company, with a query as: explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic FROM company c, traffic_logs ts WHERE c.company_id = ts.company_id AND month_trunc(ts.runtime) = '2003-10-01' GROUP BY company_name,ts.company_id; and the explain looks like: QUERY PLAN -- Aggregate (cost=32000.94..32083.07 rows=821 width=41) (actual time=32983.36..47586.17 rows=144 loops=1) - Group (cost=32000.94..32062.54 rows=8213 width=41) (actual time=32957.40..42817.88 rows=462198 loops=1) - Sort (cost=32000.94..32021.47 rows=8213 width=41) (actual time=32957.38..36261.31 rows=462198 loops=1) Sort Key: c.company_name, ts.company_id - Merge Join (cost=31321.45..31466.92 rows=8213 width=41) (actual time=13983.07..22642.14 rows=462198 loops=1) Merge Cond: (outer.company_id = inner.company_id) - Sort (cost=24.41..25.29 rows=352 width=25) (actual time=5.52..7.40 rows=348 loops=1) Sort Key: c.company_id - Seq Scan on company c (cost=0.00..9.52 rows=352 width=25) (actual time=0.02..2.78 rows=352 loops=1) - Sort (cost=31297.04..31317.57 rows=8213 width=16) (actual time=13977.49..16794.41 rows=462198 loops=1) Sort Key: ts.company_id - Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213 width=16) (actual time=0.29..5562.25 rows=462198 loops=1) Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone) Total runtime: 47587.82 msec (14 rows) the problem is that we're only taking a few months worth of data, so I don't think there is much of a way of 'improve performance' on this, but figured I'd ask quickly before I do something rash ... Note that without the month_trunc() index, the Total runtime more then doubles: QUERY PLAN Aggregate (cost=39578.63..39660.76 rows=821 width=41) (actual time=87805.47..101251.35 rows=144 loops=1) - Group (cost=39578.63..39640.23 rows=8213 width=41) (actual time=87779.56..96824.56 rows=462198 loops=1) - Sort (cost=39578.63..39599.17 rows=8213 width=41) (actual time=87779.52..90781.48 rows=462198 loops=1) Sort Key: c.company_name, ts.company_id - Merge Join (cost=38899.14..39044.62 rows=8213 width=41) (actual time=64073.98..72783.68 rows=462198 loops=1) Merge Cond: (outer.company_id = inner.company_id) - Sort (cost=24.41..25.29 rows=352 width=25) (actual time=64.66..66.55 rows=348 loops=1) Sort Key: c.company_id - Seq Scan on company c (cost=0.00..9.52 rows=352 width=25) (actual time=1.76..61.70 rows=352 loops=1) - Sort (cost=38874.73..38895.27 rows=8213 width=16) (actual
Re: [PERFORM] *very* slow query to summarize data for a month ...
Patrick Hatcher [EMAIL PROTECTED] writes: Do you have an index on ts.bytes? Josh had suggested this and after I put it on my summed fields, I saw a speed increase. What's the reasoning behind this? ISTM that sum() should never use an index, nor would it benefit from using one. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] *very* slow query to summarize data for a month ...
Marc G. Fournier [EMAIL PROTECTED] writes:
- Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213
width=16) (actual time=0.29..5562.25 rows=462198 loops=1)
Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without
time zone)
Interesting that we get the row count estimate for this index scan so
wrong -- I believe this is the root of the problem. Hmmm... I would
guess that the optimizer stats we have for estimating the selectivity
of a functional index is pretty primitive, but I haven't looked into
it at all. Tom might be able to shed some light...
[ In the second EXPLAIN ANALYZE, ... ]
- Seq Scan on traffic_logs ts (cost=0.00..38340.72 rows=8213 width=16) (actual
time=5.02..-645982.04 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) = '2003-10-01 00:00:00'::timestamp
without time zone)
Uh, what? The actual time seems to have finished far before it has
begun :-) Is this just a typo, or does the actual output include a
negative number?
-Neil
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] *very* slow query to summarize data for a month ...
Neil Conway [EMAIL PROTECTED] writes: Interesting that we get the row count estimate for this index scan so wrong -- I believe this is the root of the problem. Hmmm... I would guess that the optimizer stats we have for estimating the selectivity of a functional index is pretty primitive, but I haven't looked into it at all. Tom might be able to shed some light... Try none at all. I have speculated in the past that it would be worth gathering statistics about the contents of functional indexes, but it's still on the to-do-someday list. - Seq Scan on traffic_logs ts (cost=0.00..38340.72 rows=8213 width=16) (actual time=5.02..-645982.04 rows=462198 loops=1) Uh, what? That is bizarre, all right. Is it reproducible? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] *very* slow query to summarize data for a month ...
On Mon, 10 Nov 2003, Neil Conway wrote:
Marc G. Fournier [EMAIL PROTECTED] writes:
- Index Scan using tl_month on traffic_logs ts (cost=0.00..30763.02 rows=8213
width=16) (actual time=0.29..5562.25 rows=462198 loops=1)
Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without
time zone)
Interesting that we get the row count estimate for this index scan so
wrong -- I believe this is the root of the problem. Hmmm... I would
guess that the optimizer stats we have for estimating the selectivity
of a functional index is pretty primitive, but I haven't looked into
it at all. Tom might be able to shed some light...
[ In the second EXPLAIN ANALYZE, ... ]
- Seq Scan on traffic_logs ts (cost=0.00..38340.72 rows=8213 width=16) (actual
time=5.02..-645982.04 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) = '2003-10-01
00:00:00'::timestamp without time zone)
Uh, what? The actual time seems to have finished far before it has
begun :-) Is this just a typo, or does the actual output include a
negative number?
This was purely a cut-n-paste ...
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] *very* slow query to summarize data for a month ...
On Mon, 10 Nov 2003, Tom Lane wrote:
Neil Conway [EMAIL PROTECTED] writes:
Interesting that we get the row count estimate for this index scan so
wrong -- I believe this is the root of the problem. Hmmm... I would
guess that the optimizer stats we have for estimating the selectivity
of a functional index is pretty primitive, but I haven't looked into
it at all. Tom might be able to shed some light...
Try none at all. I have speculated in the past that it would be worth
gathering statistics about the contents of functional indexes, but it's
still on the to-do-someday list.
- Seq Scan on traffic_logs ts (cost=0.00..38340.72 rows=8213 width=16) (actual
time=5.02..-645982.04 rows=462198 loops=1)
Uh, what?
That is bizarre, all right. Is it reproducible?
Nope, and a subsequent run shows better results too:
QUERY PLAN
-
Aggregate (cost=39674.38..39756.70 rows=823 width=41) (actual
time=35573.27..49953.47 rows=144 loops=1)
- Group (cost=39674.38..39736.12 rows=8232 width=41) (actual
time=35547.27..45479.27 rows=462198 loops=1)
- Sort (cost=39674.38..39694.96 rows=8232 width=41) (actual
time=35547.23..39167.90 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
- Merge Join (cost=38993.22..39139.02 rows=8232 width=41) (actual
time=16658.23..25559.08 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=5.51..7.38 rows=348 loops=1)
Sort Key: c.company_id
- Seq Scan on company c (cost=0.00..9.52 rows=352
width=25) (actual time=0.02..2.80 rows=352 loops=1)
- Sort (cost=38968.82..38989.40 rows=8232 width=16) (actual
time=16652.66..19785.83 rows=462198 loops=1)
Sort Key: ts.company_id
- Seq Scan on traffic_logs ts (cost=0.00..38433.46
rows=8232 width=16) (actual time=0.11..8794.43 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) =
'2003-10-01 00:00:00'::timestamp without time zone)
Total runtime: 49955.22 msec
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] *very* slow query to summarize data for a month ...
On Mon, 10 Nov 2003, Josh Berkus wrote:
Marc,
I'd say your machine is very low on available RAM, particularly sort_mem.
The steps which are taking a long time are:
Here's the server:
last pid: 42651; load averages: 1.52, 0.96, 0.88
up 28+07:43:33 20:35:44
307 processes: 2 running, 304 sleeping, 1 zombie
CPU states: 18.0% user, 0.0% nice, 29.1% system, 0.6% interrupt, 52.3% idle
Mem: 1203M Active, 1839M Inact, 709M Wired, 206M Cache, 199M Buf, 5608K Free
Swap: 8192M Total, 1804K Used, 8190M Free
Aggregate (cost=32000.94..32083.07 rows=821 width=41) (actual
time=32983.36..47586.17 rows=144 loops=1)
- Group (cost=32000.94..32062.54 rows=8213 width=41) (actual
time=32957.40..42817.88 rows=462198 loops=1)
and:
- Merge Join (cost=31321.45..31466.92 rows=8213 width=41)
(actual time=13983.07..22642.14 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=5.52..7.40 rows=348 loops=1)
There are also *large* delays between steps.Either your I/O is saturated,
or you haven't run a VACUUM FULL ANALYZE in a while (which would also explain
the estimates being off).
thought about that before I started the thread, and ran it just in case ...
just restarted the server with sort_mem set to 10M, and didn't help much on the
Aggregate, or MergeJoin ... :
QUERY PLAN
-
Aggregate (cost=39674.38..39756.70 rows=823 width=41) (actual
time=33066.25..54021.50 rows=144 loops=1)
- Group (cost=39674.38..39736.12 rows=8232 width=41) (actual
time=33040.25..47005.57 rows=462198 loops=1)
- Sort (cost=39674.38..39694.96 rows=8232 width=41) (actual
time=33040.22..37875.97 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
- Merge Join (cost=38993.22..39139.02 rows=8232 width=41) (actual
time=14428.17..23568.80 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=5.80..7.66 rows=348 loops=1)
Sort Key: c.company_id
- Seq Scan on company c (cost=0.00..9.52 rows=352
width=25) (actual time=0.08..3.06 rows=352 loops=1)
- Sort (cost=38968.82..38989.40 rows=8232 width=16) (actual
time=14422.27..17429.34 rows=462198 loops=1)
Sort Key: ts.company_id
- Seq Scan on traffic_logs ts (cost=0.00..38433.46
rows=8232 width=16) (actual time=0.15..8119.72 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) =
'2003-10-01 00:00:00'::timestamp without time zone)
Total runtime: 54034.44 msec
(14 rows)
the problem is that the results we are comparing with right now is the one
that had the - time on it :( Just restarted the server with default
sort_mem, and here is the query with that:
QUERY PLAN
-
Aggregate (cost=39691.27..39773.61 rows=823 width=41) (actual
time=35077.18..50424.74 rows=144 loops=1)
- Group (cost=39691.27..39753.03 rows=8234 width=41) (actual
time=35051.29..-650049.84 rows=462198 loops=1)
- Sort (cost=39691.27..39711.86 rows=8234 width=41) (actual
time=35051.26..38847.40 rows=462198 loops=1)
Sort Key: c.company_name, ts.company_id
- Merge Join (cost=39009.92..39155.76 rows=8234 width=41) (actual
time=16155.37..25439.42 rows=462198 loops=1)
Merge Cond: (outer.company_id = inner.company_id)
- Sort (cost=24.41..25.29 rows=352 width=25) (actual
time=5.85..7.71 rows=348 loops=1)
Sort Key: c.company_id
- Seq Scan on company c (cost=0.00..9.52 rows=352
width=25) (actual time=0.10..3.07 rows=352 loops=1)
- Sort (cost=38985.51..39006.10 rows=8234 width=16) (actual
time=16149.46..19437.47 rows=462198 loops=1)
Sort Key: ts.company_id
- Seq Scan on traffic_logs ts (cost=0.00..38450.00
rows=8234 width=16) (actual time=0.16..8869.37 rows=462198 loops=1)
Filter: (date_trunc('month'::text, runtime) =
'2003-10-01 00:00:00'::timestamp without time zone)
Total runtime: 50426.80 msec
(14 rows)
And, just on a whim, here it is set to 100M:
QUERY PLAN
Re: [PERFORM] *very* slow query to summarize data for a month ...
On Mon, 10 Nov 2003, Marc G. Fournier wrote: explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic FROM company c, traffic_logs ts WHERE c.company_id = ts.company_id AND month_trunc(ts.runtime) = '2003-10-01' GROUP BY company_name,ts.company_id; What if you do ts.runtime = '2003-10-01' AND ts.runtime '2003-11-01' and add an index like (runtime, company_name, company_id)? -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
