Re: [PERFORM] index not used

If id is PK, the query shoudl return 1 row only... --- Enrico Weigelt [EMAIL PROTECTED] wrote: Hi folks, I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: idbigint

Re: [PERFORM] reltuples after vacuum and analyze

: Litao Wu [EMAIL PROTECTED] writes: I noticed that reltuples are way off if I vacuum the table and analyze the table. And the data (296901) after vacuum seems accurate while the reltuples (1.90744e+06) after anlayze is too wrong. VACUUM derives an exact count because it scans the whole

Re: [PERFORM] reltuples after vacuum and analyze

commands are done within minutes and I truely do not believe the table of 189165 rows takes that much space. Furthermore, I notice last weekly vacuum full even did not reclaim the space back. Thanks, --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: Then how to explain

Re: [PERFORM] reltuples after vacuum and analyze

Believe or not. The above command is my screen snapshot. I believe it is most possibably a PG bug! --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: reasonable size. But I do not understand why analyze bloats the table size so big?? ANALYZE won't bloat anything

[PERFORM] reltuples after vacuum and analyze

Hi, I noticed that reltuples are way off if I vacuum the table and analyze the table. And the data (296901) after vacuum seems accurate while the reltuples (1.90744e+06) after anlayze is too wrong. My PG version is 7.3.2 (I know it is old). Any thought? Thanks, my_db=# analyze my_tab;

[PERFORM] Postgres Optimizer is not smart enough?

Hi All, Here is my test comparison between Postgres (7.3.2) optimizer vs Oracle (10g) optimizer. It seems to me that Postgres optimizer is not smart enough. Did I miss anything? Thanks, In Postgres: drop table test; create table test ( modulecharacter varying(50),

Re: [PERFORM] insert waits for delete with trigger

domain_id='2005761066' AND module='spam' (26 rows) ps -elfww|grep 19027 040 S postgres 19027 870 1 69 0- 81290 semtim 07:31 ?00:00:51 postgres: postgres mxl 192.168.0.177:38266 INSERT waiting --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: Did I

Re: [PERFORM] Slow select, insert, update

Does that mean reindex is not needed for PG version 7.4? In what kind situations under PG 7.4, reindex is worthwhile? Thanks, Here is doc from 7.3: PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only

[PERFORM] insert waits for delete with trigger

Hi all, We have table q_20040805 and a delete trigger on it. The delete trigger is: update table q_summary set count=count-1... When we delete from q_20040805, we also insert into related info q_process within the same transaction. There is a PK on q_process, but no trigger on it. No FK on

Re: [PERFORM] insert waits for delete with trigger

Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: Here is info from pg_lock: All those locks are already granted, so they are not much help in understanding what PID 18951 is waiting for. What row does it have with granted = 'f' ? regards, tom lane

Re: [PERFORM] Timestamp-based indexing

Hi, How about changing: CURRENT_TIMESTAMP - INTERVAL '10 minutes' to 'now'::timestamptz - INTERVAL '10 minutes' It seems to me that Postgres will treat it as a constant. Thanks, --- Tom Lane [EMAIL PROTECTED] wrote: Matthew T. O'Connor [EMAIL PROTECTED] writes: VACUUM FULL ANALYZE every 3

[PERFORM] same plan, different time

Hi, I have a query, which runs fast for one id (query 1) and slow for other id (query 2) though both plans and cost are same except these two qeries return different number of rows. explain analyze SELECT * FROM user U LEFT JOIN user_timestamps T USING (user_id), user_alias A WHERE U.user_id =

[PERFORM] vacuum_mem

Hi, I tested vacuum_mem setting under a 4CPU and 4G RAM machine. I am the only person on that machine. The table: tablename | size_kb | reltuples ---+- big_t | 2048392 | 7.51515e+06 Case 1: 1. vacuum

[PERFORM] network address query

Hi, I have query: explain SELECT * FROM ip_tracking T, ip_map C WHERE T.source_ip::inet = C.net; QUERY PLAN --- Nested Loop (cost=0.00..3894833367750.16

Re: [PERFORM] reindex and copy - deadlock?

, process 23663 --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: One difference between these two databases is the one having REINDEX problem is using NTFS file system. Oh? That's interesting. Is it possible the root of problem? I would not expect

Re: [PERFORM] reindex and copy - deadlock?

:03 postgres: postgres db1 [local] REINDEX Here are queries from database: 23127 | REINDEX table email 3149 | COPY email (...) FROM stdin --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: It happened again. This time it hangs when we drop/create index. Here is gdb

Re: [PERFORM] reindex and copy - deadlock?

is DDL, right?) can be blocked? There must be other process(es) has/have execlusive lock on my_index, which is not our case from pg_locks. Tom, we are in the process of installing the backend with --enable-debug. Thanks, --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes

Re: [PERFORM] reindex and copy - deadlock?

in PostmasterMain () #22 0x080deed7 in main () #23 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /bin/postgres, process 18903 --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL

Re: [PERFORM] reindex and copy - deadlock?

] wrote: Litao Wu [EMAIL PROTECTED] writes: (gdb) bt #0 0x420e8bb2 in semop () from /lib/i686/libc.so.6 #1 0x080ffa28 in PGSemaphoreLock () #2 0x08116432 in LWLockAcquire () #3 0x0810f572 in LockBuffer () #4 0x0807dea3 in _bt_getbuf () #5 0x080813ec in _bt_leafbuild () #6

[PERFORM] reindex and copy - deadlock?

Hi, We often experience with the problem that reindex cannot be finished in our production database. It's typically done with 30 minutes. However, sometimes, when there is another COPY process, reindex will not finish. By monitoring the CPU time reindex takes, it does not increase at all. That

Re: [PERFORM] reindex and copy - deadlock?

access email table because of this. I will use gdb next time. What's this right way to get info as postgres owner? gdb attach pid Thanks again! --- Tom Lane [EMAIL PROTECTED] wrote: Litao Wu [EMAIL PROTECTED] writes: We often experience with the problem that reindex cannot be finished in our

Re: [PERFORM] index's relpages after table analyzed

From PG http://developer.postgresql.org/docs/postgres/diskusage.html: (Remember, relpages is only updated by VACUUM and ANALYZE.) --- Litao Wu [EMAIL PROTECTED] wrote: Hi, After a table analyzed a table, the table's relpages of pg_class gets updated, but not those of associated

Re: [PERFORM] index's relpages after table analyzed

Hi Josh, I know that and that is what I am using now. The problem is I also need to know the relpages each indexe takes and analyze seems not update relpages though vacuum and vacuum analyze do. According to PG doc: Remember, relpages is only updated by VACUUM and ANALYZE My question is why

[PERFORM] where to find out when a table was last analyzed?

All, Does PG store when a table was last analyzed? Thanks, __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP

[PERFORM] pg_stat

Hi, I have query: select pg_stat_get_numscans(76529669), pg_stat_get_blocks_fetched(76529669), pg_stat_get_blocks_hit(76529669); The result is: pg_stat_get_numscans | pg_stat_get_blocks_fetched | pg_stat_get_blocks_hit

Re: [PERFORM] sunquery and estimated rows

Well, the example shown is simplified version. Now, let's see a little 'real' example (still simplified version): Table test is same as before: \d test Table public.test Column | Type | Modifiers -+--+--- id |

[PERFORM] sunquery and estimated rows

Hi, When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. Can someone explain why? Because of this behavior, some of our queries use hash join instead of nested loop. Thanks, select version(); version