[GENERAL] Re: production java/postgresql
Dennis Muhlestein wrote: I'm interested in finding companies that use java and postgres in a production environment. If anyone knows of any url's I could visit or has experience with this in their own company, I'd love to hear about it. www.linux.org.ru FreeBSD, PostgreSQL, Java Info and konference server about Linuc on Russian Language. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: timestamp changed on 7.1.1 ?
Alex wrote: I've just install PostgreSQL 7.1.1 and noticed that timestamp have changed in this release. for example: CREATE TABLE foo ( test timestamp ); if you \d foo you will notice the column test is not a timestamp, it's a timestamp with time zone anyway, how can I make a timestamp data type be outputted without the time zone? I just want the so common -MM-DD HH:MM:SS format, not a -MM-DD HH:MM:SS-time-stamp_offset I expect to get this result *WITHOUT* rewritting existing queries. Any opinions would be great. May use RULES or TRIGGERS for formating timestamp for all SELECT commands. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Who can resolve this BUG ?
INM1=# explain select ref_num from grls where ag_id in (24); Index Scan using grls_ag_id on grls (cost=0.00..597.87 rows=849 width=12) IMN1=# explain select ref_num from grls where ag_id in (select 24); Seq Scan on grls (cost=0.00..992.44 rows=30195 width=12) SubPlan - Materialize (cost=0.00..0.00 rows=0 width=0) - Result (cost=0.00..0.00 rows=0 width=0) IMN1=# explain select ref_num from grls where ag_id = (select 24); Index Scan using grls_ag_id on grls (cost=0.00..597.87 rows=849 width=12) InitPlan - Result (cost=0.00..0.00 rows=0 width=0) . . . in (24) work in 35 faster than . . . in (select 24). In the last don't use indices for increase perfomans. It's been normaly work in PostgreSQL version 7.2 ? Or not? P.S. Sorry my English. ---(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
[GENERAL] Re: Unusual slowdown using subselects
John Aughey wrote: I'm stress testing my application by creating large data sets. This particular query selects rows from the schedule table that have a specific owner_id. (I'll show you the results of explain) calendar=# explain select * from schedule where schedule.owner_id=101 or schedule.owner_id=102; Index Scan using schedule_id_index, schedule_id_index on schedule (cost=0.00..78.64 rows=20 width=40) Looks great and executes very fast. calendar=# explain select group_id from groups where user_id=101; NOTICE: QUERY PLAN: Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1 width=4) Again, very fast. The groups table maps users to groups. However, this next one is slow. calendar=# explain select * from schedule where schedule.owner_id in (select group_id from groups where user_id=101); NOTICE: QUERY PLAN: Seq Scan on schedule (cost=0.00..2039895.00 rows=100 width=40) SubPlan - Materialize (cost=2.02..2.02 rows=1 width=4) - Index Scan using groups_id_index on groups (cost=0.00..2.02 rows=1 width=4) In my DB: # explain SELECT * FROM grls WHERE grls.ag_id = 24; NOTICE: QUERY PLAN: Index Scan using grls_ag_id on grls (cost=0.00..597.87 rows=849 width=122) # explain SELECT ag_id FROM agncs WHERE ag_id = 24; NOTICE: QUERY PLAN: Seq Scan on agncs (cost=0.00..1.31 rows=1 width=4) And together: # explain select * from grls where grls.ag_id in (select ag_id from agncs where ag_id = 24); NOTICE: QUERY PLAN: Seq Scan on grls (cost=0.00..40623.38 rows=30195 width=122) SubPlan - Materialize (cost=1.31..1.31 rows=1 width=4) - Seq Scan on agncs (cost=0.00..1.31 rows=1 width=4) -- # select count(*) from grls; 30195 Summarize - with subselect indices ignores and search look all DB rows. IT'S BUG. P.S. Sorry my English. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Why index don't use with SELECT
IMN1=# explain select count(*) from grls WHERE active = 10; NOTICE: QUERY PLAN: Aggregate (cost=993.21..993.21 rows=1 width=0) - Seq Scan on grls (cost=0.00..992.44 rows=307 width=0) IMN1=# explain select count(*) from grls WHERE popularity = 10; NOTICE: QUERY PLAN: Aggregate (cost=23.22..23.22 rows=1 width=0) - Index Scan using grls_popularity on grls (cost=0.00..23.17 rows=20 width=0) IMN1=# \di grls grls_active | index | alex grls_popularity | index | alex Why SELECT use Index grls_popularity and don't use index grls_active ? Both create and VACUUM ANALYZE grls? It's a BUG? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Re: Tiiiiiiiiiiiiime
hiroko wrote: from a DB table, I'd like to SELECT out the date of 3months before. the command should be = select * from MYTBL where time =XX ; what whould it like to be at X ? Try this: SELECT * FROM mytbl WHERE create_date (now() - '3 month'::interval) ; See date/time function manual. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] Parallel Serever use PostgreSQL
Help me, please. Can I create a 2 Parallel Server with clustrering DB PostgreSQL? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Bug with timestamp !!! (resolve)
create table tmp (create_datetimestamp);
#insert into tmp values('2001-04-01 02:29:52');
INSERT 1021715 1
#select * from tmp;
create_data
2035-05-29 01:33:36-05
(1 row)
Before start PostgreSQL on FreeBSD. Set environment variable TZ (time zone).
For me work this command:
TZ=+00; export TZ
or
TZ=+00 pg_ctl -o -i -B 512 -D
---(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
