On 10/10/2010 11:02 AM, Reid Thompson wrote:
On Sat, Oct 9, 2010 at 5:26 PM, Neil Whelchel<neil.whelc...@gmail.com>
On the other hand, I copied a table out of one of my production servers that
has about 60,000 rows with 6 columns (numeric, numeric, bool, bool, timestamp,
text). The first numeric column has numbers evenly spread between 0 and 100
and it is indexed. I put the table in a pair of database servers both running
on the same physical hardware. One server is Postgres, the other is a popular
server (I am not mentioning names here). on Postgres: SELECT count(*) FROM
table where column>50; takes about 8 seconds to run. The other database server
took less than one second (about 25 ms) as it is using the index (I assume) to
come up with the results. It is true that this is not a fair test because both
servers were tested with their default settings, and the defaults for Postgres
are much more conservative, however, I don't think that any amount of settings
tweaking will bring them even in the same ball park. There has been discussion
about the other server returning an incorrect count because all of the indexed
rows may not be live at the time. This is not a problem for the intended use,
that is why I suggested another function like estimate(*). It's name suggests
that the result will be close, not 100% correct, which is plenty good enough
for generating a list of results pages in most cases. I am faced with a very
serious problem here. If the query to make a list of pages takes say 6 seconds
and it takes another 6 seconds to generate a page of results, the customer is
waiting 12 seconds. This is not going to work. If count made a quick estimate,
say less than a second, and it took 6 seconds to come up with the actual
results, I could live with that. Or if coming up with the window of results
via (OFFSET and LIMIT) and returned the total number of rows that would have
matched the query, then I would still have everything I need to render the
page in a reasonable time. I really think that this needs to be addressed
somewhere. It's not like I am the only one that does this. You see it nearly
everywhere a long list of results is (expected to be) returned in a web site.
Among the people I work with, this seems to be the most mentioned reason that
they claim that they don't use Postgres for their projects. t anyway.
How big is your DB?
How fast is your disk access?
Any chance disks/RAM can be addressed?
My disk access is pitiful...
first run, 2.3 million rows.. 0m35.38s, subsequent runs.. real 0m2.55s
rthom...@hw-prod-repdb1> time psql -c "select count(*) from
my_production_table" reporting
count
---------
2340704
(1 row)
real 0m35.38s
user 0m0.25s
sys 0m0.03s
subsequent runs.... (count changes due to inserts.)
rthom...@hw-prod-repdb1> time psql -c "select count(*) from
my_production_table" reporting
count
---------
2363707
(1 row)
real 0m2.70s
user 0m0.27s
sys 0m0.02s
rthom...@hw-prod-repdb1> time psql -c "select count(*) from
my_production_table" reporting
count
---------
2363707
(1 row)
real 0m2.55s
user 0m0.26s
sys 0m0.02s
rthom...@hw-prod-repdb1> time psql -c "select count(*) from
my_production_table" reporting
count
---------
2363707
(1 row)
real 0m2.50s
user 0m0.26s
sys 0m0.02s
reporting=# SELECT
pg_size_pretty(pg_total_relation_size('my_production_table'));
pg_size_pretty
----------------
1890 MB
(1 row)
forgot to note, my table schema is significantly larger.
rthom...@hw-prod-repdb1> time psql -c "\d my_production_table_201010" reporting
Table
"public.my_production_table_201010"
Column | Type |
Modifiers
-----------------------------+-----------------------------+----------------------------------------------------------------
| integer | not null default
nextval('my_production_table_parent_id_seq'::regclass)
| character varying(20) |
| character(1) |
| character varying(32) |
| character varying(32) |
| character varying(20) |
| character varying(5) |
| character varying(5) |
| date |
| character(1) |
| character varying(32) |
| character varying(32) |
| character varying(32) |
| character varying(2) |
| character varying(10) |
| character varying(10) |
| character varying(32) |
| character varying(7) |
| character varying(10) |
| character varying(2) |
| character varying(9) |
| character varying(9) |
| character varying(9) |
| character varying(10) |
| character varying(32) |
| character varying(32) |
| character varying(20) |
| character varying(5) |
| character varying(5) |
| character varying(32) |
| character varying(32) |
| character varying(32) |
| character varying(2) |
| character varying(10) |
| character varying(10) |
| character varying(10) |
| character varying(10) |
| integer |
| character varying(2) |
| character varying(32) |
| character varying(32) |
| integer |
| integer |
| text |
| character varying(3) |
| date |
| date |
| date |
| integer |
| integer |
| integer |
| integer |
| character varying(6) |
| character varying(10) |
| character varying(32) |
| character varying(32) |
| character varying(32) |
| character varying(10) |
| character varying(6) |
| character varying(8) |
| boolean |
| character(1) |
| date |
| integer |
| date |
| character varying(11) |
| character varying(4) |
| character(1) |
| date |
| character varying(5) |
| character varying(20) |
| date |
| character(1) |
| character(1) |
| character varying(2) |
| text |
| integer |
| integer |
| timestamp without time zone | default now()
| timestamp without time zone |
| character varying(64) |
| character varying(64) |
| character varying(64) |
Indexes:
"my_production_table_201010_pkey" PRIMARY KEY, btree (id)
"my_production_table_201010_date_idx" btree (xxxxdate), tablespace
"indexspace"
"my_production_table_201010_epatient_idx" btree (storeid, xxxxxxxxxxxxx), tablespace
"indexspace"
"my_production_table_201010_medicationname_idx" btree (xxxxxxxxxxxxxx), tablespace
"indexspace"
"my_production_table_201010_ndc_idx" btree (xxx), tablespace "indexspace"
Check constraints:
"my_production_table_201010_filldate_check" CHECK (xxxxdate >= '2010-10-01'::date AND xxxxdate <
'2010-11-01'::date)
Foreign-key constraints:
"my_production_table_201010_pkgfileid_fkey" FOREIGN KEY (pkgfileid)
REFERENCES my_production_tablefiles(id)
Inherits: my_production_table_parent
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
