1

Using PostgreSQL I have a few tables that are about 22,000 records and about 275 columns. These tables are actually reports. There are about 25 sections in each report containing anywhere from 4-12 columns. It is data related to particular items and then various percentiles by demographic, region, etc. that ultimately end up in a published Excel file.

Whether running a:

SELECT * FROM table

Or on an indexed column:

SELECT * FROM table WHERE year IS NOT NULL

It still takes about 22 minutes to retrieve all of the results for every year.

I could probably normalize the table and split each of these 25 or so sections into their own table and then have 25 or so joins, but not sure that it would be any quicker.

I see that similar questions have been asked and ultimately I probably just need to take the time to test it.

EDIT:

PostreSQL: PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Queries: SELECT * FROM table

Or on an indexed column:

SELECT * FROM table WHERE year IS NOT NULL

SQL for example table:

-- Table: public.cdr_rpt

-- DROP TABLE public.cdr_rpt;

CREATE TABLE public.cdr_rpt
(
    year integer NOT NULL,
    code numeric(12, 2) NOT NULL,
    category character varying(255) COLLATE pg_catalog."default" NOT NULL,
    posttle character varying(255) COLLATE pg_catalog."default" NOT NULL,
    cut character varying(255) COLLATE pg_catalog."default" NOT NULL,
    cat character varying(255) COLLATE pg_catalog."default" NOT NULL,
    currency character varying(255) COLLATE pg_catalog."default" NOT NULL,
    basicincn integer,
    basicinc10 numeric(19, 2),
    basicinc25 numeric(19, 2),
    basicincmedian numeric(19, 2),
    basicincmean numeric(19, 2),
    basicinc65 numeric(19, 2),
    basicinc75 numeric(19, 2),
    basicinc90 numeric(19, 2),
    basiccon integer,
    basiccomedian numeric(19, 2),
    basiccomean numeric(19, 2),
    prdcon integer,
    prdcomedian numeric(6, 3),
    prdcomean numeric(6, 3),
    prdcotcmedian numeric(6, 3),
    prdcotcmean numeric(6, 3),
    prdcotdmedian numeric(6, 3),
    prdcotdmean numeric(6, 3),
    targettcincn integer,
    targettcinc10 numeric(19, 2),
    targettcinc25 numeric(19, 2),
    targettcincmedian numeric(19, 2),
    targettcincmean numeric(19, 2),
    targettcinc65 numeric(19, 2),
    targettcinc75 numeric(19, 2),
    targettcinc90 numeric(19, 2),
    targettccon integer,
    targettccomedian numeric(19, 2),
    targettccomean numeric(19, 2),
    pctincn integer,
    pctinc10 numeric(6, 3),
    pctinc25 numeric(6, 3),
    pctincmedian numeric(6, 3),
    pctincmean numeric(6, 3),
    pctinc65 numeric(6, 3),
    pctinc75 numeric(6, 3),
    pctinc90 numeric(6, 3),
    pctcon integer,
    pctcomedian numeric(6, 3),
    pctcomean numeric(6, 3),
    tcincn integer,
    tcinc10 numeric(19, 2),
    tcinc25 numeric(19, 2),
    tcincmedian numeric(19, 2),
    tcincmean numeric(19, 2),
    tcinc65 numeric(19, 2),
    tcinc75 numeric(19, 2),
    tcinc90 numeric(19, 2),
    tccon integer,
    tccomedian numeric(19, 2),
    tccomean numeric(19, 2),
    ltargetvalincn integer,
    ltargetvalinc10 numeric(19, 2),
    ltargetvalinc25 numeric(19, 2),
    ltargetvalincmedian numeric(19, 2),
    ltargetvalincmean numeric(19, 2),
    ltargetvalinc65 numeric(19, 2),
    ltargetvalinc75 numeric(19, 2),
    ltargetvalinc90 numeric(19, 2),
    ltargetvalcon integer,
    ltargetvalcomedian numeric(19, 2),
    ltargetvalcomean numeric(19, 2),
    ltaddincn integer,
    ltaddinc10 numeric(19, 2),
    ltaddinc25 numeric(19, 2),
    ltaddincmedian numeric(19, 2),
    ltaddincmean numeric(19, 2),
    ltaddinc65 numeric(19, 2),
    ltaddinc75 numeric(19, 2),
    ltaddinc90 numeric(19, 2),
    ltaddcon integer,
    ltaddcomedian numeric(19, 2),
    ltaddcomean numeric(19, 2),
    lpctbasicincn integer,
    lpctbasicinc10 numeric(6, 3),
    lpctbasicinc25 numeric(6, 3),
    lpctbasicincmedian numeric(6, 3),
    lpctbasicincmean numeric(6, 3),
    lpctbasicinc65 numeric(6, 3),
    lpctbasicinc75 numeric(6, 3),
    lpctbasicinc90 numeric(6, 3),
    lpctbasiccon integer,
    lpctbasiccomedian numeric(6, 3),
    lpctbasiccomean numeric(6, 3),
    targettdincn integer,
    targettdinc10 numeric(19, 2),
    targettdinc25 numeric(19, 2),
    targettdincmedian numeric(19, 2),
    targettdincmean numeric(19, 2),
    targettdinc65 numeric(19, 2),
    targettdinc75 numeric(19, 2),
    targettdinc90 numeric(19, 2),
    targettdcon integer,
    targettdcomedian numeric(19, 2),
    targettdcomean numeric(19, 2),
    tdincn integer,
    tdinc10 numeric(19, 2),
    tdinc25 numeric(19, 2),
    tdincmedian numeric(19, 2),
    tdincmean numeric(19, 2),
    tdinc65 numeric(19, 2),
    tdinc75 numeric(19, 2),
    tdinc90 numeric(19, 2),
    tdcon integer,
    tdcomedian numeric(19, 2),
    tdcomean numeric(19, 2),
    ltaddongincn integer,
    ltaddonginc10 numeric(19, 2),
    ltaddonginc25 numeric(19, 2),
    ltaddongincmedian numeric(19, 2),
    ltaddongincmean numeric(19, 2),
    ltaddonginc65 numeric(19, 2),
    ltaddonginc75 numeric(19, 2),
    ltaddonginc90 numeric(19, 2),
    ltaddongcon integer,
    ltaddongcomedian numeric(19, 2),
    ltaddongcomean numeric(19, 2),
    lpctbasicongincn integer,
    lpctbasiconginc10 numeric(6, 3),
    lpctbasiconginc25 numeric(6, 3),
    lpctbasicongincmedian numeric(6, 3),
    lpctbasicongincmean numeric(6, 3),
    lpctbasiconginc65 numeric(6, 3),
    lpctbasiconginc75 numeric(6, 3),
    lpctbasiconginc90 numeric(6, 3),
    lpctbasicongcon integer,
    lpctbasicongcomedian numeric(6, 3),
    lpctbasicongcomean numeric(6, 3),
    tdongincn integer,
    tdonginc10 numeric(19, 2),
    tdonginc25 numeric(19, 2),
    tdongincmedian numeric(19, 2),
    tdongincmean numeric(19, 2),
    tdonginc65 numeric(19, 2),
    tdonginc75 numeric(19, 2),
    tdonginc90 numeric(19, 2),
    tdongcon integer,
    tdongcomedian numeric(19, 2),
    tdongcomean numeric(19, 2),
    revincn integer,
    revincmedian numeric(19, 2),
    revincmean numeric(19, 2),
    revcon integer,
    revcomedian numeric(19, 2),
    revcomean numeric(19, 2),
    nummatchcon integer,
    nummatchincn integer,
    suptotalcon integer,
    suptotalincn integer,
    suptotalincmean integer,
    ex1incn integer,
    ex1incpct numeric(6, 3),
    ex2incn integer,
    ex2incpct numeric(6, 3),
    match1incn integer,
    match1incpct numeric(6, 3),
    match2incn integer,
    match2incpct numeric(6, 3),
    match3incn integer,
    match3incpct numeric(6, 3),
    regcode1incn integer,
    regcode1incpct numeric(6, 3),
    regcode2incn integer,
    regcode2incpct numeric(6, 3),
    regcode3incn integer,
    regcode3incpct numeric(6, 3),
    regcode4incn integer,
    regcode4incpct numeric(6, 3),
    regcode5incn integer,
    regcode5incpct numeric(6, 3),
    regcode6incn integer,
    regcode6incpct numeric(6, 3),
    stelig0con integer,
    stelig0copct numeric(6, 3),
    stelig1con integer,
    stelig1copct numeric(6, 3),
    stelig0incn integer,
    stelig0incpct numeric(6, 3),
    stelig1incn integer,
    stelig1incpct numeric(6, 3),
    anystrecd0incn integer,
    anystrecd0incpct numeric(6, 3),
    anystrecd1incn integer,
    anystrecd1incpct numeric(6, 3),
    havttarget0con integer,
    havttarget0copct numeric(6, 3),
    havttarget1con integer,
    havttarget1copct numeric(6, 3),
    tottargetcon integer,
    tottargetco25 numeric(19, 2),
    tottargetcomedian numeric(19, 2),
    tottargetcomean numeric(19, 2),
    tottargetco75 numeric(19, 2),
    actvtargetcon integer,
    actvtargetincn integer,
    actvtargetincmean numeric(6, 3),
    lelig0con integer,
    lelig0copct numeric(6, 3),
    lelig1con integer,
    lelig1copct numeric(6, 3),
    lelig0incn integer,
    lelig0incpct numeric(6, 3),
    lelig1incn integer,
    lelig1incpct numeric(6, 3),
    lrecd0incn integer,
    lrecd0incpct numeric(6, 3),
    lrecd1incn integer,
    lrecd1incpct numeric(6, 3),
    sls1con integer,
    sls1copct numeric(6, 3),
    sls200con integer,
    sls200copct numeric(6, 3),
    sls3000con integer,
    sls3000copct numeric(6, 3),
    sls99999con integer,
    sls99999copct numeric(6, 3),
    sls1incn integer,
    sls1incpct numeric(6, 3),
    sls200incn integer,
    sls200incpct numeric(6, 3),
    sls3000incn integer,
    sls3000incpct numeric(6, 3),
    slstarget1con integer,
    slstarget1co25 numeric(6, 3),
    slstarget1comedian numeric(6, 3),
    slstarget1comean numeric(6, 3),
    slstarget1co75 numeric(6, 3),
    slstarget2con integer,
    slstarget2co25 numeric(6, 3),
    slstarget2comedian numeric(6, 3),
    slstarget2comean numeric(6, 3),
    slstarget2co75 numeric(6, 3),
    slstarget3con integer,
    slstarget3co25 numeric(6, 3),
    slstarget3comedian numeric(6, 3),
    slstarget3comean numeric(6, 3),
    slstarget3co75 numeric(6, 3),
    slstarget4con integer,
    slstarget4co25 numeric(6, 3),
    slstarget4comedian numeric(6, 3),
    slstarget4comean numeric(6, 3),
    slstarget4co75 numeric(6, 3),
    revtargetteamcfyincn integer,
    revtargetteamcfyinc10 numeric(19, 2),
    revtargetteamcfyinc25 numeric(19, 2),
    revtargetteamcfyincmedian numeric(19, 2),
    revtargetteamcfyincmean numeric(19, 2),
    revtargetteamcfyinc65 numeric(19, 2),
    revtargetteamcfyinc75 numeric(19, 2),
    revtargetteamcfyinc90 numeric(19, 2),
    revtargetteamcfycon integer,
    revtargetteamcfycomedian numeric(19, 2),
    revtargetteamcfycomean numeric(19, 2),
    revtargetindvcfyincn integer,
    revtargetindvcfyinc10 numeric(19, 2),
    revtargetindvcfyinc25 numeric(19, 2),
    revtargetindvcfyincmedian numeric(19, 2),
    revtargetindvcfyincmean numeric(19, 2),
    revtargetindvcfyinc65 numeric(19, 2),
    revtargetindvcfyinc75 numeric(19, 2),
    revtargetindvcfyinc90 numeric(19, 2),
    revtargetindvcfycon integer,
    revtargetindvcfycomedian numeric(19, 2),
    revtargetindvcfycomean numeric(19, 2),
    CONSTRAINT cdr_rpt_pkey PRIMARY KEY (year, code, cat, cut, currency)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.cdr_rpt
    OWNER to zzz;

-- Index: cdr_rpt_category

-- DROP INDEX public.cdr_rpt_category;

CREATE INDEX cdr_rpt_category
    ON public.cdr_rpt USING btree
    (category COLLATE pg_catalog."en_US.utf8" bpchar_pattern_ops)
    TABLESPACE pg_default;

-- Index: cdr_rpt_code

-- DROP INDEX public.cdr_rpt_code;

CREATE INDEX cdr_rpt_code
    ON public.cdr_rpt USING btree
    (code)
    TABLESPACE pg_default;

-- Index: cdr_rpt_cat

-- DROP INDEX public.cdr_rpt_cat;

CREATE INDEX cdr_rpt_cat
    ON public.cdr_rpt USING btree
    (cat COLLATE pg_catalog."en_US.utf8" bpchar_pattern_ops)
    TABLESPACE pg_default;

-- Index: cdr_rpt_cut

-- DROP INDEX public.cdr_rpt_cut;

CREATE INDEX cdr_rpt_cut
    ON public.cdr_rpt USING btree
    (cut COLLATE pg_catalog."en_US.utf8" bpchar_pattern_ops)
    TABLESPACE pg_default;

-- Index: cdr_rpt_year

-- DROP INDEX public.cdr_rpt_year;

CREATE INDEX cdr_rpt_year
    ON public.cdr_rpt USING btree
    (year)
    TABLESPACE pg_default;

That should address the relevant questions. Can't make a SQLFiddle.

closed as primarily opinion-based by mustaccio, Max Vernon, James Anderson, billinkc, RLF Mar 2 '17 at 20:46

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • If your table is only for reporting have you indexed it heavily yet? – wogsland Feb 28 '17 at 18:13
  • So although it is a report, in the following year it is used to compare the previous year's results with the current year's. (It is a fairly lengthy process of collecting and cleaning data and the previous results are continuously read in at each stage). There are a handful of fields that are used for these JOINs and I have each of those indexed. So about 4 to 5 columns are indexed in the 275. Primarily year, item number, and two others I can't provide. – sockpuppet Feb 28 '17 at 18:19
  • 2
    How much data does a single row contain? Retrieving 22k rows in 22 minutes is very slow, unless a row contains something like a GB of data. – dezso Feb 28 '17 at 19:10
  • Please read: stackoverflow.com/tags/postgresql-performance/info then edit your question and add the missing information. – a_horse_with_no_name Feb 28 '17 at 21:41
  • I don't know what you would gain by splitting data into separate tables and adding a join? – paparazzo Mar 1 '17 at 17:48
1

OP reported

select count(*) FROM table WHERE year IS NOT NULL  

Ran in like one second
At the database the query runs in like one second

This is just a data volume thing getting data to the client.
22 minutes seems too long but that is the problem to solve.

Not the answer you're looking for? Browse other questions tagged or ask your own question.