Re: Surprise :-( - Mailing list pgsql-general

Only one of the 350k trxtype=MP records has isposted=false !!!
I am downloading 7.2.2 right now, and, God willing, on Monday I will be able
to apply your recommendations regarding explain analyze.
Which leads me to the following question:
I dump this database with pgdump in less than 15 minutes and restore it with
psql -d mydata -f mydata.dmp in 375 minutes.
Is there a faster way?
Thanks again.

-----Original Message-----
From: Nigel J. Andrews <nandrews@investsystems.co.uk>
To: Mihai Gheorghiu <tanethq@earthlink.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Friday, September 06, 2002 2:27 PM
Subject: Re: [GENERAL] Surprise :-(


>On Fri, 6 Sep 2002, Mihai Gheorghiu wrote:
>
>> PG7.1.3 on RH7.1 on Dell PowerEdge 2500SC P3-933, 1GB RAM, 18GB SCSI160
>> There are 350k rows with trxtype=MP
>> With indexscan=off:
>> Aggregate  (cost=22975.15..22990.27 rows=302 width=24)
>>   ->  Group  (cost=22975.15..22982.71 rows=3025 width=24)
>>         ->  Sort  (cost=22975.15..22975.15 rows=3025 width=24)
>>               ->  Seq Scan on tbas_transactions  (cost=0.00..22800.29
>> rows=3025 width=24)
>> Time: 25.9s
>> With indexscan=on:
>> Aggregate  (cost=10874.64..10889.76 rows=302 width=24)
>>   ->  Group  (cost=10874.64..10882.20 rows=3025 width=24)
>>         ->  Sort  (cost=10874.64..10874.64 rows=3025 width=24)
>>               ->  Index Scan using trx_trxtype_idx on tbas_transactions
>> (cost=0.00..10699.78 rows=3025 width=24)
>> Time: 24.9s
>> The point is I need to run this query in a fraction of the above time,
>> otherwise I'm in deep trouble.
>> Any suggestion is welcome.
>>
>
>Yes, drop the index on trxtype so that it's selecting on isposted.
Obviously,
>knowing things like the actual number of rows returned by explain analyze
(only
>7.2.x?) as per my other message would be more usedul.
>
>Your trxtype index is actually selecting 50% of the rows but thinks it only
has
>3025...hmmmm...typing that made me think something is wrong...ah, I see 7.1
.3,
>not 7.2.x...how many other values for this field are there? Even 7.1.3
should
>have caught that as a common value right? However, in short, unless
isposted is
>true for lots of those 350k rows you dropping the index would hopefully
prompt
>a switch to an index on that column. Or, create a multicolumn index on
>(isposted,trxtype).
>
>BTW, there must be some sort of data clustering going because that index
scan
>is faster than the sequential scan even though it's fetching 50% of the
>table. Perhaps it's a result of caching.
>
>
>--
>Nigel J. Andrews
>Director
>
>---
>Logictree Systems Limited
>Computer Consultants
>